T-SQL 练习整理

收集一些SQL题作为练习，题目来源于网络，答案自己写或者网络，留记录，非标准。

题目

1。表 ExamGrade(ID,CLASS,USERNAME,SUBJECT,Grade), 统计每个班总的考试人数，考语文的人数和总分。

生成表如下

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

View Code

分析

要求中有分别统计班级的结果会用到 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