1-6 用关联子查询比较行与行-SQL进阶教程

增长、减少、维持现状

-- 求与上一年营业额一样的年份(1)：使用关联子查询
select year,sale
from Sales S1
where sale=(select sale from Sales S2 where S2.year=S1.year-1)
order by year;

-- 求与上一年营业额一样的年份(2)：使用自连接
select S1.year,S1.sale
from Sales S1,Sales S2
where S2.sale=S1.sale
and S2.year=S1.year-1
order by year;

-- 求出是增长了还是减少了，抑或是维持现状(1)：使用关联子查询
select S1.year,S1.sale,
(case 
	when sale=(select sale from Sales S2 where S2.year=S1.year-1) then '→'
	when sale>(select sale from Sales S2 where S2.year=S1.year-1) then '↑'
	when sale<(select sale from Sales S2 where S2.year=S1.year-1) then '↓'
	else '-'
end) var
from Sales S1
order by year;

-- 求出是增长了还是减少了，抑或是维持现状(2)：使用自连接查询（最早的年份不会出现）
select S1.year,S1.sale,
(case
	when S1.sale=S2.sale then '→'
	when S1.sale>S2.sale then '↑'
	when S1.sale<S2.sale then '↓'
	else '-'
end) var
from Sales S1,Sales S2
where S2.year=S1.year-1
order by year;

时间轴有间断时：和过去最临近的时间进行比较

-- 查询与过去最临近的年份营业额相同的年份
select year,sale
from Sales2 S1
where sale=(
select sale
from Sales2 S2
where S2.year=(
	select max(year) -- 条件2 ：在满足条件1的年份中，年份最早的一个
	from Sales2 S3
	where S1.year>S3.year -- 条件1 ：与该年份相比是过去的年份
	) 
)
order by year;

-- 查询与过去最临近的年份营业额相同的年份：使用自连接
select S1.year year,S1.sale sale
from Sales2 S1,Sales2 S2
where S1.sale=S2.sale
and S2.year=(
	select max(year)
	from Sales2 S3
	where S1.year>S3.year
)
order by year;

-- 求每一年与过去最临近的年份之间的营业额之差(1)：结果里不包含最早的年份
select S2.year pre_year,S1.year now_year,S2.sale pre_sale,S1.sale now_sale,
S1.sale-S2.sale diff
from Sales2 S1,Sales S2
where S2.year=(
	select max(year)
	from Sales2 S3
	where S1.year>S3.year
)
order by now_year;

-- 求每一年与过去最临近的年份之间的营业额之差(2)：使用自外连接。结果里包含最早的年份
select S2.year pre_year,S1.year now_year,S2.sale pre_sale,S1.sale now_sale,
S1.sale-S2.sale diff
from Sales2 S1
left join Sales S2
on S2.year=(
	select max(year)
	from Sales2 S3
	where S1.year>S3.year
)
order by now_year;

移动累计值和移动平均值

-- 求累计值：使用窗口函数
select prc_date,prc_amt,
sum(prc_amt) over (order by prc_date) onhand_amt
from Accounts;

-- 求累计值：使用冯·诺依曼型递归集合
select prc_date,A1.prc_amt,
(
	select sum(prc_amt)
	from Accounts A2
	where A1.prc_date>=A2.prc_date
) onhand_amt
from Accounts A1
order by prc_date;

-- 求移动累计值(1)：使用窗口函数
select prc_date,prc_amt,
sum(prc_amt) over (order by prc_date rows 2 preceding) onhand_amt
from Accounts;

-- 求移动累计值(2)：不满3行的时间区间也输出
select prc_date,A1.prc_amt,
(
	select sum(prc_amt)
	from Accounts A2
	where A1.pc_date>=A2.prc_date
	and(
		select count(*)
		from Accounts A3
		where A3.prc_date between A2.prc_date and A1.prc_date
	)<=3
) mvg_sum
from Accounts A1
order by prc_date;

-- 移动累计值(3)：不满3行的区间按无效处理
select prc_date,A1.prc_amt,
(
	select sum(prc_amt)
	from Accounts A2
	where A1.pc_date>=A2.prc_date
	and(
		select count(*)
		from Accounts A3
		where A3.prc_date between A2.prc_date and A1.prc_date
	)<=3
	having count(*)=3 -- 不满3行数据的不显示
) mvg_sum
from Accounts A1
order by prc_date;

查询重叠的时间区间

-- 求重叠的住宿期间
select reserver,start_date,end_date
from Reservations R1
where exists(
	select *
	from Reservations R2
	where R1.reserver<>R2.reserver -- 与自己以外的客人进行比较
	and(
		R1.start_date between R2.start_date and R2.end_date
		-- 条件(1)：自己的入住日期在他人的住宿期间内
		or R1.end_date between R2.start_date and R2.end_date
		-- 条件(2)：自己的离店日期在他人的住宿期间内
		)
);

-- 升级版：把完全包含别人的住宿期间的情况也输出
select reserver,start_date,end_date
from Reservations R1
where exists(
	select *
	from Reservations R2
	where R1.reserver<>R2.reserver
	and(
			(
			R1.start_date between R2.start_date and R2.end_date
			or R1.end_date between R2.start_date and R2.end_date
			)
		or(
			R2.start_date between R1.start_date and R1.end_date
			and R2.end_date between R1.start_date and R1.end_date
		)
	)
);