--学生基本信息表：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
结果不一样啊 求高手解答啊
------解决方案--------------------
--学生基本信息表：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