数据统计处理的有关问题.多谢-MSSQL教程-爱易网页

数据统计处理的有关问题.多谢

日期：2014-05-18　浏览次数：20519 次

数据统计处理的问题.谢谢.
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 StuCur
END
*/

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

数据统计处理的有关问题.多谢

相关资料更多>

推荐阅读更多>