1-9练习题-SQL进阶教程

练习题1-9-1 ：求所有的缺失编号——not exists和外连接


正文中提到过，SQL里有很多方法可以实现差集运算。正文里介绍了使用except和not in实现的方法。请思考一下使用not exists和外连接实现的方法

-- not exists版
select seq
from Sequence S
where seq between 1 and 12
and not exists(
	select * from SeqTbl ST
	where S.seq=ST.seq
);

-- 外连接版
select S.seq
from Sequence S
left join SeqTbl ST
on S.seq=ST.seq
where S.seq between 1 and 12
and ST.seq is null;

练习题1-9-2 ：求序列——面向集合的思想



在“三个人能坐得下吗”部分，我们用not exists表达了全称量化，进而求出了序列。请思考一下如何使用having子句来解决这个问题
在解决了不考虑换排的情况之后，请再思考一下考虑换排的情况

-- 不换排
select S1.seat start_seat,'~',S2.seat end_seat
from Seats S1,Seats S2,Seats S3
where S2.seat=S1.seat+(:head_cnt-1)
and S3.seat between S1.seat and S2.seat
group by S1.seat,S2.seat
having count(*)=sum(case when S3.status='未预订' then 1 else 0 end);

-- 换排
select S1.seat start_seat,'~',S2.seat end_seat
from Seats S1,Seats S2,Seats S3
where S2.seat=S1.seat+(:head_cnt-1)
and S3.seat between S1.seat and S2.seat
group by S1.seat,S2.seat
having count(*)=sum(case when S3.status='未预订' and S3.row_id=S1.row_id then 1 else 0 end);

练习题1-9-3 ：求所有的序列——面向集合的思想

这里练习用集合论的方法去完成与谓词逻辑同样的功能。在“最多能坐下多少人”部分，我们使用not exists求出了所有的序列，并将它们存储在了视图里。请将那条 SQL语句用having子句改写。思路和练习题1-9-2大致相同，只不过特征函数的条件稍微复杂一些

select S1.seat start_seat,S2.seat end_seat,S2.seat-S1.seat+1 seat_cnt
from Seats S1,Seats S2,Seats S3
where S1.seat<=S2.seat
and S3.seat between S1.seat-1 and S2.seat+1
group by S1.seat,S2.seat
having count(*)=sum(
	case
		when S3.seat between S1.seat and S2.seat and S3.status='未预订' then 1
		when S3.seat=S2.seat+1 and S3.status='已预定' then 1
		when S3.seat=S2.seat-1 and S3.status='已预定' then 1
		else 0
	end
);