各位大神 跪求sql

[weiguo_]

--学生基本信息表：TEST_STUDENT
create table TEST_STUDENT(
STUDENT_NO CHAR(4),
STUDENT_NAME VARCHAR(20),
STUDENT_SEX CHAR(2) ,
)
--选修课程表:TEST_SUBJECT
create table TEST_SUBJECT(
SUBJECT_NO CHAR(2),
SUBJECT_NAME VARCHAR(50)
)
--学生选修课程情况表：TEST_ELECTIVE_INFO
create table TEST_ELECTIVE_INFO(
ELECTIVE_INFO_ID INT,
STUDENT_NO CHAR(4),
SUBJECT_NO CHAR(2)
)
--学生选修课程成绩表 TEST_GRADE_INFO
create table TEST_GRADE_INFO(
GRADE_INFO_ID INT,
STUDENT_NO CHAR(4),
SUBJECT_NO CHAR(2),
SUBJECT_SCORE INT
)
--初始化数据

INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0001','学生1','男')
INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0002','学生2','女')
INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0003','学生3','男')
INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0004','学生4','女')
INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0005','学生5','男')

INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('01','计算机基础')
INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('02','数据结构')
INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('03','计算机原理')
INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('04','概率论与数理统计')
INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('05','离散数学')
INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('06','网络系统')

INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (1,'0001','01')
INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (2,'0001','02')
INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (3,'0001','03')
INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (4,'0002','01')
INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (5,'0002','02')
INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (6,'0002','03')

INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (1,'0001','01',85)
INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (2,'0001','02',90)
INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (3,'0001','03',95)
INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (4,'0002','01',85)
INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (5,'0002','02',90)
INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (6,'0002','03',95)

要实现如图结果
我写的sql
select st.student_no,student_name,subject_name ,subject_score from test_student st
inner join test_grade_info gr on st.student_no = gr.student_no
inner join test_subject su on su.subject_no = gr.subject_no
UNION all
select '' ,'平均值' , '' , sum(subject_score) as subject_score
from test_grade_info group by student_no
UNION all
select '' ,'总分' , '' , avg(subject_score) as subject_score
from test_grade_info group by student_no
结果不一样啊 求高手解答啊

[jiajiaren]

--学生基本信息表：TEST_STUDENT

IF OBJECT_ID(N'TEST_STUDENT') IS NOT NULL
DROP TABLE TEST_STUDENT
GO
create table TEST_STUDENT(
 STUDENT_NO     CHAR(4),
    STUDENT_NAME   VARCHAR(20),
    STUDENT_SEX    CHAR(2) ,
)
--选修课程表:TEST_SUBJECT
IF OBJECT_ID(N'TEST_SUBJECT') IS NOT NULL
DROP TABLE TEST_SUBJECT
GO
create table TEST_SUBJECT(
SUBJECT_NO     CHAR(2),
    SUBJECT_NAME   VARCHAR(50)
)
--学生选修课程情况表：TEST_ELECTIVE_INFO
IF OBJECT_ID(N'TEST_ELECTIVE_INFO') IS NOT NULL
DROP TABLE TEST_ELECTIVE_INFO
GO
create table TEST_ELECTIVE_INFO(
  ELECTIVE_INFO_ID    INT,
    STUDENT_NO     CHAR(4),
    SUBJECT_NO     CHAR(2)
)
--学生选修课程成绩表 TEST_GRADE_INFO
IF OBJECT_ID(N'TEST_GRADE_INFO') IS NOT NULL
DROP TABLE TEST_GRADE_INFO
GO
create table TEST_GRADE_INFO(
GRADE_INFO_ID  INT,
    STUDENT_NO     CHAR(4),
    SUBJECT_NO     CHAR(2),
    SUBJECT_SCORE  INT 
)
--初始化数据

INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0001','学生1','男')
    INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0002','学生2','女')
    INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0003','学生3','男')
    INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0004','学生4','女')
    INSERT INTO TEST_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_SEX) VALUES ('0005','学生5','男')

    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('01','计算机基础')
    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('02','数据结构')
    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('03','计算机原理')
    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('04','概率论与数理统计')
    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('05','离散数学')
    INSERT INTO TEST_SUBJECT(SUBJECT_NO,SUBJECT_NAME) VALUES ('06','网络系统')

    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (1,'0001','01')
    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (2,'0001','02')
    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (3,'0001','03')
    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (4,'0002','01')
    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (5,'0002','02')
    INSERT INTO TEST_ELECTIVE_INFO(ELECTIVE_INFO_ID,STUDENT_NO,SUBJECT_NO) VALUES (6,'0002','03')

    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (1,'0001','01',85)
    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (2,'0001','02',90)
    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (3,'0001','03',95)
    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (4,'0002','01',85)
    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (5,'0002','02',90)
    INSERT INTO TEST_GRADE_INFO(GRADE_INFO_ID,STUDENT_NO,SUBJECT_NO,SUBJECT_SCORE) VALUES (6,'0002','03',95)


SELECT  student_no ,student_name,subject_name,subject_score  FROM 
(
select st.student_no,student_name,subject_name ,subject_score,  gr.student_no AS sort from test_student st
inner join test_grade_info gr on st.student_no = gr.student_no
inner join test_subject su on su.subject_no = gr.subject_no  
UNION all
select '' ,'总分' , '' , sum(subject_score) as subject_score ,  student_no AS sort
from test_grade_info  group by student_no 
UNION all
select '' ,'平均值' , '' , avg(subject_score) as subject_score ,  student_no AS sort
from test_grade_info  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                  计算机原理                                              95
0002       学生2                  数据结构                                               90
0002       学生2                  计算机基础                                              85
           平均值                                                                     90
           总分                                                                      270

(10 行受影响)

 */