各位大神 跪求sql
--学生基本信息表: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
结果不一样啊 求高手解答啊
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
结果不一样啊 求高手解答啊
...全文
相关推荐
查看死锁信息.sql
此代码运行后,会生成一个存储过程,后面直接调用此存储过程即可查看,运行非常方便,可以快速定位死锁进程,快速解决sql数据库死锁问题。希望可以给各位系统管理员带来方便。希望可以给各位系统管理员带来方便。希望可以给各位系统管理员带来方便。
SQL导出Excel源码
SQL导出excel源码,看了以后提高下各位对SQL的操作
SQL-10进制,16进制转换
SQL进制转换函数,方便快捷,希望能够帮到各位老铁。。。。。。。。。。。。。。。。。。
SQL语句经典教程
学习SQL 语句的基础教程,让各位想轻松学习到SQL语言知识的人更加得心应手
sql server实现递归查询的方法示例
本文实例讲述了sql server实现递归查询的方法示例。分享给大家供大家参考,具体如下: 有时候面对树结构的数据时需要进行递归查询,网上找了一番,参考了各位大神的文章,发现蛮简单的,当做个小笔记方便以后使用 sql server 通过CTE来支持递归查询,这对查询树形或层次结构的数据很有用 一般的树形表结构如下,相信大家都很熟悉的 id title pid 1 1级节点 0 2 2级节点 1 3 3级节点 2 4 4级节点 3 5 5级节点 4 下面上代码 ----------sql server 递归查询---------- --查找上级所有节点 with


