MSSQL 2005 有两个表分别是
Student
ID StuNo Name Grade Class
1 800001 陈明 1 A
2 800002 张华 2 A
3 800003 李月 3 A
4 800004 黎顺 1 B
5 800005 许肖基 2 B
6 800006 肖晨咕 3 B
7 800007 杨晨明 1 A
8 800008 戴三 2 A
9 800009 黄英 3 A
10 800010 张三 4 A
11 800011 李四 5 A
12 800012 黄五 4 A
13 800013 陈七 5 A
Score
StuNo Mark
800001 3
800002 77
800003 92
800004 47
800005 82
800006 24
800007 7
800008 94
800009 72
800010 73
800011 6
800012 78
800013 96
求结果如下的SQL语句
标题 Grade Class StuNo Mark
1 A 800001 3
1 A 800007 7
班总分 1 A NULL 10
级总分 1 NULL NULL 57
1 B 800004 47
班总分 1 B NULL 47
2 A 800002 77
2 A 800008 94
班总分 2 A NULL 171
级总分 2 NULL NULL 253
2 B 800005 82
班总分 2 B NULL 82
3 A 800003 92
3 A 800009 72
班总分 3 A NULL 164
级总分 3 NULL NULL 188
3 B 800006 24
班总分 3 B NULL 24
4 A 800010 73
4 A 800012 78
班总分 4 A NULL 151
级总分 4 NULL NULL 151
5 A 800011 6
5 A 800013 96
班总分 5 A NULL 102
级总分 5 NULL NULL 102
总分 NULL NULL NULL 751
SQL脚本如下:
- SQL code
--DROP TABLE Student;CREATE TABLE Student(ID INT IDENTITY(1, 1) PRIMARY KEY,StuNo INT UNIQUE,Name VARCHAR(128),Grade INT,Class VARCHAR(10));--DROP TABLE Score;CREATE TABLE Score(StuNo INT FOREIGN KEY REFERENCES Student(StuNo),Mark INT);INSERT INTO Student VALUES (800001, '陈明', 1, 'A');INSERT INTO Student VALUES (800002, '张华', 2, 'A');INSERT INTO Student VALUES (800003, '李月', 3, 'A');INSERT INTO Student VALUES (800004, '黎顺', 1, 'B');INSERT INTO Student VALUES (800005, '许肖基', 2, 'B');INSERT INTO Student VALUES (800006, '肖晨咕', 3, 'B');INSERT INTO Student VALUES (800007, '杨晨明', 1, 'A');INSERT INTO Student VALUES (800008, '戴三', 2, 'A');INSERT INTO Student VALUES (800009, '黄英', 3, 'A');INSERT INTO Student VALUES (800010, '张三', 4, 'A');INSERT INTO Student VALUES (800011, '李四', 5, 'A');INSERT INTO Student VALUES (800012, '黄五', 4, 'A');INSERT INTO Student VALUES (800013, '陈七', 5, 'A');/*BEGIN DECLARE StuCur CURSOR FOR SELECT StuNo FROM Student; DECLARE @StrBuffer AS VARCHAR(1024); OPEN StuCur; FETCH NEXT FROM StuCur INTO @StrBuffer; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'INSERT INTO Score VALUES(' + @StrBuffer + ', ' + CAST(CAST(RAND() * 100 AS INT) AS VARCHAR) + ')' FETCH NEXT FROM StuCur INTO @StrBuffer; END DEALLOCATE StuCurEND*/INSERT INTO Score VALUES(800001, 53)INSERT INTO Score VALUES(800002, 77)INSERT INTO Score VALUES(800003, 92)INSERT INTO Score VALUES(800004, 47)INSERT INTO Score VALUES(800005, 82)INSERT INTO Score VALUES(800006, 24)INSERT INTO Score VALUES(800007, 73)INSERT INTO Score VALUES(800008, 94)INSERT INTO Score VALUES(800009, 72)INSERT INTO Score VALUES(800010, 73)INSERT INTO Score VALUES(800011, 63)INSERT INTO Score VALUES(800012, 78)INSERT INTO Score VALUES(800013, 96)------解决方案--------------------
- SQL code
CREATE TABLE Student(ID INT IDENTITY(1, 1) PRIMARY KEY,StuNo INT UNIQUE,Name VARCHAR(128),Grade INT,Class VARCHAR(10))go--DROP TABLE Score;CREATE TABLE Score(StuNo INT FOREIGN KEY REFERENCES Student(StuNo),Mark INT)INSERT INTO Student VALUES (800001, '陈明', 1, 'A');INSERT INTO Student VALUES (800002, '张华', 2, 'A');INSERT INTO Student VALUES (800003, '李月', 3, 'A');INSERT INTO Student VALUES (800004, '黎顺', 1, 'B');INSERT INTO Student VALUES (800005, '许肖基', 2, 'B');INSERT INTO Student VALUES (800006, '肖晨咕', 3, 'B');INSERT INTO Student VALUES (800007, '杨晨明', 1, 'A');INSERT INTO Student VALUES (800008, '戴三', 2, 'A');INSERT INTO Student VALUES (800009, '黄英', 3, 'A');INSERT INTO Student VALUES (800010, '张三', 4, 'A');INSERT INTO Student VALUES (800011, '李四', 5, 'A');INSERT INTO Student VALUES (800012, '黄五', 4, 'A');INSERT INTO Student VALUES (800013, '陈七', 5, 'A');INSERT INTO Score VALUES(800001, 53)INSERT INTO Score VALUES(800002, 77)INSERT INTO Score VALUES(800003, 92)INSERT INTO Score VALUES(800004, 47)INSERT INTO Score VALUES(800005, 82)INSERT INTO Score VALUES(800006, 24)INSERT INTO Score VALUES(800007, 73)INSERT INTO Score VALUES(800008, 94)INSERT INTO Score VALUES(800009, 72)INSERT INTO Score VALUES(800010, 73)INSERT INTO Score VALUES(800011, 63)INSERT INTO Score VALUES(800012, 78)INSERT INTO Score VALUES(800013, 96)goselect CASE WHEN (GROUPING(a.Grade) = 1) THEN '总分' ELSE ISNULL(cast(a.Grade as varchar), 'UNKNOWN') END AS 总分, --'总分'), a.Class, a.StuNo, sum(b.mark) as markfrom Student aleft join Score b on a.stuno = b.stunogroup by a.grade,a.class,a.stuno with rollup drop table Score,Student/*总分 Class StuNo mark ------------------------------ ---------- ----------- ----------- 1 A 800001 531 A 800007 731 A NULL 1261 B 800004 471 B NULL 471 NULL NULL 1732 A 800002 772 A 800008 942 A NULL 1712 B 800005 822 B NULL 822 NULL NULL 2533 A 800003 923 A 800009 723 A NULL 1643 B 800006 243 B NULL 243 NULL NULL 1884 A 800010 734 A 800012 784 A NULL 1514 NULL NULL 1515 A 800011 635 A 800013 965 A NULL 1595 NULL NULL 159总分 NULL NULL 924（所影响的行数为 27 行）*/