正文
这里我们会遇到subquery，它可以出现在select子句中或者where子句或者from子句中。它会产生一个对应的结果表格，我们可以给这个表示命名。
数据集
我们这一篇文章采用PostgreSQL的SQL语法。重点我们关注select...from...where这种读操作，分析query (analytical query)。
数据集在 https://hyper-db.de/interface.html 可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。
架构 Schema:
下载:
https://db.in.tum.de/teaching/ws1920/grundlagen/uni_mysql.sql?lang=de
Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。
课件：
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/?lang=de
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/pdf/Kapitel4.pdf?lang=de
书： https://db.in.tum.de/teaching/bookDBMSeinf/?lang=de
中级SQL
- 在pruefen中搜索note小于平局值的：
select *
from pruefen
where note < (
select avg(note)
from pruefen
)
- 对每一个professoren，对应的vorlesungen的sws求和：
-- correlated sub-query
select p.persnr, p.name, (
select sum(v.sws) as lehrbelastung
from vorlesungen v
where v.gelesenvon = p.persnr
)
from professoren p
-- no sub-query
select p.persnr, p.name, sum(sws)
from professoren p left outer join vorlesungen v on p.persnr = v.gelesenvon
group by p.name, p.persnr
- 搜索上课数大于２的学生：
select tmp.matrnr, tmp.name, tmp.vorlanzahl
from (select s.matrnr, s.name, count(*) as vorlanzahl
from studenten s, hoeren h
where s.matrnr = h.matrnr
group by s.matrnr, s.name) tmp
where tmp.vorlanzahl > 2
这时候我们对这个subquery的结果表格进行命名tmp。当然我们可以用with子句来做同样的事情。我主观上更喜欢用with,它很清晰地把暂时需要的表格写在最上方，而且对debug也更加友好。当然两者是结果等价，运行时间也等价的。
with tmp as (select s.matrnr, s.name, count(*) as vorlanzahl
from studenten s, hoeren h
where s.matrnr = h.matrnr
group by s.matrnr, s.name)
select tmp.matrnr, tmp.name, tmp.vorlanzahl
from tmp
where tmp.vorlanzahl > 2
- 计算每一个vorlesungen的人数占比：
select h.vorlnr, h.anzProVorl, g.gesamtAnz, cast(h.anzProVorl as decimal(6, 1)) / g.gesamtAnz as MarkAnteil
from (select vorlnr, count(*) as anzProVorl
from hoeren
group by vorlnr) as h,
(select count(*) as gesamtAnz
from studenten) g
-- with子句版本
with h as (select vorlnr, count(*) as anzProVorl
from hoeren
group by vorlnr),
g as (select count(*) as gesamtAnz
from studenten)
select h.vorlnr, h.anzProVorl, g.gesamtAnz, cast(h.anzProVorl as decimal(6, 1)) / g.gesamtAnz as MarkAnteil
from h, g
- 计算每一个professoren通过上课认识的studenten个数以及比例：
with kenntSich as (
select distinct v.gelesenvon as profpersnr, h.matrnr as studmatrnr
from hoeren h join vorlesungen v on h.vorlnr =v.vorlnr
),
kenntAnzahl as (
select profpersnr, count(*) as anzstudenten
from kenntSich
group by profpersnr),
wieviel as (
select count(*) as gesamtanz
from studenten)
select k.profpersnr, p.name, k.anzstudenten, w.gesamtanz, 1.00 * k.anzstudenten / w.gesamtanz as bekanntheitsgard
from kenntAnzahl k, wieviel w, professoren p
where k.profpersnr = p.persnr
order by bekanntheitsgard desc
- 搜索听了所有sws=4 vorlesungen的学生：
SELECT s.*
FROM studenten s
where not exists(
select *
from vorlesungen v
where v.sws = 4 and not exists(
select *
from hoeren h
where h.vorlnr = v.vorlnr and h.matrnr = s.matrnr
)
)
SQL92中没有定义for all Quantifier(全称量词)。所以我们只能改写关系代数：
我们先把改写成
:
再把改写成
:
再用DeMorgan律简化一下：
用中文说：不存在一门sws=4的课，没有被这个学生听。这样我们可以对应关系代数到上面的SQL。
</br>
另外一种trick解法，使用count:
-- 先把hoeren变成sws=4hoeren: hoerenStudentenWith4SWS
with hoerenStudentenWith4SWS (matrnr, vorlnr) as (
select h.matrnr, v.vorlnr
from hoeren h, vorlesungen v
where h.vorlnr = v.vorlnr and v.sws = 4
)
-- 再看学生是不是听完了所有hoerenStudentenWith4SWS
select h.matrnr
from hoerenStudentenWith4SWS h
group by h.matrnr
having count(*) = (select count(*) from vorlesungen v where v.sws = 4)
- (对上面的类似练习) 搜索学生所有考过的试对应的科目，都是这个同学所听过：
select s.*
from studenten s
where not exists(
select *
from pruefen p
where p.matrnr = s.matrnr and not exists(
select *
from hoeren h
where h.vorlnr = p.vorlnr and h.matrnr = s.matrnr
)
)
用中文说：没有一门被考过的科目，没有出现在对应学生hoeren表格中。
另外因为这个要求是独立得应用在每一个学生上，每一个学生因为考试不同，所有要求听的科目也不同。因此上面那题的trick不再适用。trick应用条件是对所有学生需要普遍性，而排除独立性 -- 一视同仁。
- (对上面的类似练习) 搜索学生所有听过的科目，都考试并通过(
note<=4)：
select *
from Studenten s
where not exists (
select *
from hoeren h
where h.MatrNr = s.MatrNr and not exists (
select *
from pruefen p
where p.MatrNr = s.MatrNr and p.VorlNr = h.VorlNr and p.Note <= 4
)
)
用中文说：没有一门上过课的科目，没有出现在对应学生pruefen表格中并没有通过。
这个依旧很难用trick。
- 求至少听Sokrates一门课的学生们的平均学期数：
with vl_von_sokrates as (
select *
from vorlesungen v, professoren p
where v.gelesenvon = p.persnr and p.name = 'Sokrates'
), studenten_von_sokrates as (
select distinct s.name, s.matrnr, s.semester
from studenten s, hoeren h, vl_von_sokrates v
where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
)
select avg(semester)
from studenten_von_sokrates;
这题一定要注意,可能一个学生听了Sokrates的很多课，但是这种同学不能被重复计数。我们可以用distinct。
但是我们也有一种解法不需要distinct，它不用join,而是带exists的correlated subquery:
with vl_von_sokrates as (
select *
from vorlesungen v, professoren p
where v.gelesenvon = p.persnr and p.name = 'Sokrates'
), studenten_von_sokrates as (
select *
from studenten s
where exists(
select *
from hoeren h, vl_von_sokrates vl
where h.matrnr = s.matrnr and h.vorlnr = vl.vorlnr
)
)
select avg(semester)
from studenten_von_sokrates;
- 求每个学生听几节课，需要考虑不听任何课的学生：
select count(*) as hcount
from hoeren
),
s as (
select count(*) as scount
from studenten
)
select hcount / (scount * 1.00) as avg_vl
from h, s
或者
with h as (
select count(*) as hcount
from hoeren
),
s as (
select count(*) as scount
from studenten
)
select hcount / (cast(scount as decimal(10, 4))) as avg_vl
from h, s
该文章遵循创作共用版权协议 CC BY-NC 3.0，要求署名、非商业 、保持一致。在满足创作共用版权协议 CC BY-NC 3.0 的基础上可以转载，但请以超链接形式注明出处。文章仅代表作者的知识和看法，如有不同观点，可以回复并讨论。
