字段名称 字段类型 ELECTIVE_INFO_ID INT STUDENT_NO CHAR(4) SUBJECT_NO CHAR(2) 学生选修课程成绩表 TEST_GRADE_INFO
GRADE_INFO_ID INT STUDENT_NO CHAR(4) SUBJECT_NO CHAR(2) SUBJECT_SCORE INT
IF OBJECT_ID(N'Test') IS NOT NULL DROP TABLE Test GO CREATE TABLE Test(student_no VARCHAR(10),student_name VARCHAR(10),subject_name VARCHAR(10),subject_score INT) INSERT INTO Test SELECT '0001', '学生1','计算机基础',85 UNION ALL SELECT '0001', '学生1','数据结构',90 UNION ALL SELECT '0001', '学生1','计算机原理',95UNION ALL SELECT '0002', '学生2','计算机基础',85 UNION ALL SELECT '0002', '学生2','数据结构',90 UNION ALL SELECT '0002', '学生2','计算机原理',95 SELECT student_no ,student_name,subject_name,subject_score FROM ( SELECT student_no,student_name,subject_name,subject_score,student_no AS sort FROM Test UNION ALL SELECT '' AS student_no, '平均分' student_name,'' subject_name,AVG(subject_score) AS avg_score, student_no AS sort FROM Test GROUP BY student_no UNION ALL SELECT '' AS student_no, '总分' student_name,'' subject_name,SUM(subject_score) AS sum_score,student_no AS sort FROM Test GROUP BY student_no )a ORDER BY sort ASC,(CASE student_name WHEN '平均分' THEN 1 WHEN '总分' THEN 2 ELSE 0 END) ASC
字段名称 字段类型
STUDENT_NO CHAR(4)
STUDENT_NAME VARCHAR(20)
STUDENT_SEX CHAR(2) 选修课程表:TEST_SUBJECT
字段名称 字段类型
SUBJECT_NO CHAR(2)
SUBJECT_NAME VARCHAR(50) 学生选修课程情况表:TEST_ELECTIVE_INFO
字段名称 字段类型
ELECTIVE_INFO_ID INT
STUDENT_NO CHAR(4)
SUBJECT_NO CHAR(2) 学生选修课程成绩表 TEST_GRADE_INFO
GRADE_INFO_ID INT
STUDENT_NO CHAR(4)
SUBJECT_NO CHAR(2)
SUBJECT_SCORE INT
DROP TABLE Test
GO
CREATE TABLE Test(student_no VARCHAR(10),student_name VARCHAR(10),subject_name VARCHAR(10),subject_score INT)
INSERT INTO Test
SELECT '0001', '学生1','计算机基础',85
UNION ALL SELECT '0001', '学生1','数据结构',90
UNION ALL SELECT '0001', '学生1','计算机原理',95UNION ALL SELECT '0002', '学生2','计算机基础',85
UNION ALL SELECT '0002', '学生2','数据结构',90
UNION ALL SELECT '0002', '学生2','计算机原理',95
SELECT student_no ,student_name,subject_name,subject_score FROM
(
SELECT student_no,student_name,subject_name,subject_score,student_no AS sort FROM Test
UNION ALL
SELECT '' AS student_no, '平均分' student_name,'' subject_name,AVG(subject_score) AS avg_score, student_no AS sort FROM Test GROUP BY student_no
UNION ALL
SELECT '' AS student_no, '总分' student_name,'' subject_name,SUM(subject_score) AS sum_score,student_no AS sort FROM Test GROUP BY student_no
)a
ORDER BY sort ASC,(CASE student_name WHEN '平均分' THEN 1 WHEN '总分' THEN 2 ELSE 0 END) ASC
/*
student_no student_name subject_name subject_score
---------- ------------ ------------ -------------
0001 学生1 计算机基础 85
0001 学生1 数据结构 90
0001 学生1 计算机原理 95
平均分 90
总分 270
0002 学生2 计算机基础 85
0002 学生2 数据结构 90
0002 学生2 计算机原理 95
平均分 90
总分 270(10 行受影响)
*/