数据统计处理的问题.谢谢.
MSSQL 2005 有两个表分别是
Student
IDStuNoNameGradeClass
1800001陈明1A
2800002张华2A
3800003李月3A
4800004黎顺1B
5800005许肖基2B
6800006肖晨咕3B
7800007杨晨明1A
8800008戴三2A
9800009黄英3A
10800010张三4A
11800011李四5A
12800012黄五4A
13800013陈七5A
Score
StuNoMark
8000013
80000277
80000392
80000447
80000582
80000624
8000077
80000894
80000972
80001073
8000116
80001278
80001396
求结果如下的SQL语句
标题GradeClassStuNoMark
1A8000013
1A8000077
班总分1ANULL10
级总分1NULLNULL57
1B80000447
班总分1BNULL47
2A80000277
2A80000894
班总分2ANULL171
级总分2NULLNULL253
2B80000582
班总分2BNULL82
3A80000392
3A80000972
班总分3ANULL164
级总分3NULLNULL188
3B80000624
班总分3BNULL24
4A80001073
4A80001278
班总分4ANULL151
级总分4NULLNULL151
5A8000116
5A80001396
班总分5ANULL102
级总分5NULLNULL102
总分NULLNULLNULL751
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 行）*/
[解决办法]
- SQL code
--作出一可正确排序的表select case when id=0 then '' when id=1 then '班总分' else '级总分' end 标题, grade,case when class='Z' then null else class end class1,stuno,markfrom ( select 0 id,a.grade,a.class,a.stuno,b.markfrom student a,score bwhere a.stuno=b.stunounion allselect 1 id,a.grade,a.class,null studno,sum(b.mark) markfrom student a,score bwhere a.stuno=b.stunogroup by a.grade,a.classunion allselect 2 id,a.grade,'Z' class,null studno,sum(b.mark) markfrom student a,score bwhere a.stuno=b.stunogroup by a.grade ) aorder by grade,class,id