T-SQL 练习整理
收集一些SQL题作为练习,题目来源于网络,答案自己写或者网络,留记录,非标准。
题目
1。表 ExamGrade(ID,CLASS,USERNAME,SUBJECT,Grade), 统计每个班总的考试人数,考语文的人数和总分。
生成表如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
IF OBJECT_ID('ExamGrade') IS NOT NULL DROP TABLE ExamGrade; CREATE TABLE ExamGrade( ID INT IDENTITY(1,1), Class NVARCHAR(20), UserName NVARCHAR(20), Subjects NVARCHAR(20), Grade INT ); GO INSERT INTO ExamGrade(Class,UserName,Subjects,Grade) SELECT 'Class1','Lily','Math',90 UNION ALL SELECT 'Class1','Lily','Chinese',89 UNION ALL SELECT 'Class1','Tom','Math',26 UNION ALL SELECT 'Class1','Tom','Chinese',31 UNION ALL SELECT 'Class2','Lucy','Math',90 UNION ALL SELECT 'Class2','Lucy','Chinese',80 UNION ALL SELECT 'Class2','Jerry','Math',70 ; GO
分析
要求中有分别统计班级的结果会用到 GRGOUP BY,既有整体统计又有分科统计,会用到表与表之间的JOIN。
解答
SELECT S.Class AS Class,SUM(Grade) AS SumGrade, COUNT(DISTINCT UserName) AS SumStuAttendExam, SumStuAttendChsExam FROM ExamGrade AS S LEFT JOIN (SELECT Class, COUNT(UserName) AS SumStuAttendChsExam FROM ExamGrade WHERE Subjects = 'Chinese' GROUP BY Class ) AS P ON S.Class=P.Class GROUP BY S.Class,SumStuAttendChsExam ORDER BY S.Class; GO
结果