快捷通道
什么是派生表
派生表是Microsoft公司新推出的SQL2005数据库的一个增强功能。它将子查询做为一个表来处理,这个由子查询得出的新表就是我们说的“派生表”。
派生表:比如要查找一个叫张铁牛的人的信息,我们知道他是男性,为了缩小查找范围我把所有的男性都找出来,然后从这些男性中里面再去找张铁牛。这里男性的集合就相当于派生表,转成sql语句是这样:
select 姓名,住址,身份证 from (select * from 表名 where 性别='男性') t where 姓名='张铁牛'
,这里的t这个数据集就是派生表,它是虚表,在数据库中不存在的,是我们构建的,在这里的目的是为了缩小数据的查找范围。
派生表的用法
派生表本身性能上并没有什么优点,要看怎么用,就能提高整个查询的性能。比如要查询学生的姓名以及他们每一门分数之和。
下面这种查询方法效率很低。每一条学生数据都会去查询一次tb_score表。
SELECT Name,
(select sum(score) from tb_score where tb_student.id=tb_score.studentid) As SumScore
from tb_student
而下面这种方式,使用学生表关联“学生的分数之和派生表",从而避免了每一条数据都要去执行一次查询。
SELECT Name,
tb_scoresum.ScoreSum
from tb_student
inner join (select studentid,sum(score) as ScoreSum
from tb_score
group by studentid) tb_scoresum on tb_student.id=tb_scoresum.studentid
10-23 查询平均成绩以上的课程(MSSQL)
分数 10
全屏浏览题目
切换布局
作者 张庆
单位 集美大学
本题目要求编写SQL语句,
查询每个同学超过他选修的平均成绩的课程。
请使用:
1)用相关子查询实现
2)使用派生表实现。
表结构:
请在这里写定义表结构的SQL语句。例如:
-- 课程表
CREATE TABLE cou (
cno char(4) NOT NULL,
cname nvarchar(30) NOT NULL,
credit smallint DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE sc (
sno char(4) NOT NULL, -- 学生学号
cno char(4) NOT NULL, -- 课程号
grade decimal(4,1) DEFAULT NULL,
PRIMARY KEY ( sno , cno ),
CONSTRAINT fk_sc_cno FOREIGN KEY ( cno ) REFERENCES cou ( cno )
)
表样例
请在这里给出上述表结构对应的表样例。例如
cou
表:
sc
表:
输出样例:
请在这里给出输出样例。例如:
先建立一个sc 的派生表来存储平均成绩,然后连接sc与cou表
select a.sno 学号,cou.cname 课程名,a.grade 成绩
from sc a join cou
on a.cno=cou.cno
where a.grade >
(select avg(b.grade) from sc b where a.sno=b.sno)
order by grade