基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，此时子查询生成的临时派生表成为主查询的查询对象。

---

参考书目：《数据库系统概论》第5版 王珊著 实验环境：ORACLE、MySQL、Navicat 实验语言：SQL

---

前言

这两周比较忙，在打比赛，做很多大数据的实验，就（被迫）拖更了。今天吃完午饭在休息，朋友突然小窗大喊“救命”，我以为发生甚么事了，原来是他做了一个早上的数据库作业，就差最后一题。看了一眼，要做派生表，火速打开电脑帮他解题（再牛的人也要上机实验对吧！何况是我><）。借这个机会，更一篇派生表的笔记吧！

---

一、学生-课程数据库

原始基本表SC的数据如下：

【例】找出每个学生超过他自己选修课程平均成绩的课程号。

1.生成派生表

首先找出每个学生的个人所有选修课程的平均成绩，查看学号、此人所有选修课程的平均成绩。

select sno,avg(grade)
from sc
group by sno;

结果如下：

2.连接基本表与派生表

将此派生表命名为Avg_sc，字段分别为avg_sno和avg_grade，并与表SC做等值连接。
这里，根据课本的意思，SQL语句是这样写的：

select *
from sc,(select sno,avg(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade)
where sc.sno=avg_sc.sno;

但是，实验时，由于我使用的是Oracle数据库，对派生表的命名不需要使用as，也不支持命名派生表时通过avg_sc(avg_sno,avg_grade)的方式命名派生表的列：

经过我的数次尝试，Oracle中对派生表及其列的命名的SQL语句可以这样写：

select *
from sc,(select sno as avg_sno,avg(grade)as avg_grade from sc group by sno) avg_sc
where sc.sno=avg_sc.avg_sno;

结果如下：

3.选择

由上一步，可以看出最后的查询结果应该包含以下红框中的数据行：

所以，最后一步要对查询结果做选择，筛选需要的数据行与字段列。

select sno,cno
from sc,(select sno as avg_sno,avg(grade)as avg_grade from sc group by sno) avg_sc
where sc.sno=avg_sc.avg_sno and sc.grade>=avg_sc.avg_grade;

结果如下：

二、仓库库存数据库

（小小吐槽一下自己，Excel导入Navicat的时候忘记设置表中各字段的编码方式，导入失败好几次！！）
略去Excel中的数据导入数据库的环节，原始基本表stock的数据如下：

【例】查询库存量超过所在仓库平均库存量的物资名称和库存量。

1.生成派生表

首先查出每个仓库库存的平均值：

select warehouse,avg(amount)
from stock
group by warehouse;

结果如下：

2.连接基本表与派生表

将此派生表命名为avg_stock，字段分别为avg_warehouse和avg_amount，并与表stock做等值连接。
这里，同Oracle，那种方式的派生表及其列名的自定义，在MySQL中也不让用。。所以这样写：

select *
from stock,(select warehouse as avg_warehouse,avg(amount) as avg_amount from stock group by warehouse) as avg_stock
where stock.warehouse=avg_stock.avg_warehouse;

结果如下：

3.选择

由上一步，可以看出最后的查询结果应该包含以下红框中的数据行：

所以，最后一步要对查询结果做选择，筛选需要的数据行与字段列。

select stock.mat_name,stock.amount
from stock,(select warehouse as avg_warehouse,avg(amount) as avg_amount from stock group by warehouse) avg_stock
where stock.warehouse=avg_stock.avg_warehouse and stock.amount>avg_stock.avg_amount;

结果如下：

---

总结

好累好累好累！终于写完了！吃个晚饭回去继续写大数据的实验报告了！这类题目乍一看很难做，但是把问题分解成几个小步骤，思路也就理清楚了，那还是不难做的。希望我的笔记对看到这里的你有帮助~