Sql Group by 使用

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

CREATE TABLE StuCourseScore ( ID int, Name nvarchar(10), Course nvarchar(10), Score int ) INSERT StuCourseScore VALUES(1,'张三','语文',80) INSERT StuCourseScore VALUES(1,'张三','数学',89) INSERT StuCourseScore VALUES(1,'张三','英语',90)   INSERT StuCourseScore VALUES(2,'李四','语文',100) INSERT StuCourseScore VALUES(2,'李四','数学',79) INSERT StuCourseScore VALUES(2,'李四','英语',95)   SELECT * FROM StuCourseScore   SELECT --group的字段或者聚合函数     ID,     sum(     case         when Course='语文' then Score     end) as "语文成绩",     sum(     case         when Course='数学' then Score     end) as "数学成绩",     sum(     case         when Course='英语' then Score     end) as "英语成绩" FROM StuCourseScore GROUP BY ID   --以后名字加上 SELECT Name FROM StuCourseScore group BY Name