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

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

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

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

相关资料更多>

  1. 查询视图反复行赋值
  2. 这个语句的有关问题和含义在那里呢
  3. ：如何执行查询语句
  4. 关于一条SQl语句,该如何解决
  5. sql 语句实现,该如何处理
  6. 对全部数据库启用跨数据库的所有权连接【sql 2000】
  7. 求列统计,该怎么解决
  8. 待，关于存储过程、自定义函数方面的有关问题
  9. 对导出数据制标记的有关问题