1-6用关联子查询比较行

最新推荐文章于 2023-02-18 15:29:43 发布

Myzoneliver

最新推荐文章于 2023-02-18 15:29:43 发布

阅读量208

点赞数

分类专栏：《SQL进阶教程》摘录

《SQL进阶教程》摘录专栏收录该内容

9 篇文章 0 订阅

订阅专栏

前言

使用 SQL 对同一行数据进行列间的比较很简单，只需要在 WHERE 子句里写上比较条件就可以了，例如 col_1 = col_2 。但是，对不同行数据进行列间的比较却没那么简单。使用 SQL 进行行间比较时，发挥主要作用的技术是关联子查询

用列表展示与上一年的比较结果

查询和去年相比, 销售额的趋势
在这里插入图片描述
使用子查询

SELECT s.`year`,s.sale,
case 
when s.sale = (select sale from sales where year = s.`year`-1) then '持平'
when s.sale > (select sale from sales where year = s.`year`-1) then '上升'
when s.sale < (select sale from sales where year = s.`year`-1) then '下降'
else '-' end as '趋势'
from sales s;

使用自连接. 采用这种实现方法时，由于这里没有 1990 年之前的数据，所以 1990年会被排除掉，执行结果会少一行。

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 AS var
FROM Sales S1, Sales S2
WHERE S2.year = S1.year - 1
ORDER BY year;

上面是书上的答案, 看起来不顺, 理一理, 先内连接
在这里插入图片描述
看清楚字段再判断:

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

有年份缺失, 这样一来，“年份 - 1 ”这个条件就不能用了。我们需要把它扩展成更普遍的情况，用某一年的数据和它过去最临近的年份进行比较。
在这里插入图片描述
查询与过去最临近的年份营业额相同的年份

-- 子查询
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)) -- 条件１ ：与该年份相比是过去的年份
ORDER BY year;

-- 自连接，可以减少一层子查询的嵌套。
SELECT S1.year AS year,
S1.year AS year
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;

移动累计值和移动平均值

在这里插入图片描述
求截止到那个时间点的账户余额

-- 求累计值：使用窗口函数
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
FROM Accounts;

-- 求累计值：使用冯· 诺依曼型递归集合
select a.prc_date,a.prc_amt,
(select sum(b.prc_amt)
from accounts b where a.prc_date>=b.prc_date
) as onhand_amount
from accounts a;

结果

在这里插入图片描述

-- 求移动累计值(1)：使用窗口函数
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date
ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;

-- 求移动累计值(2)：不满3 行的时间区间也输出
select a.prc_date,a.prc_amt,
(select sum(b.prc_amt)
from accounts b where a.prc_date>=b.prc_date 
and (select count(*) from accounts c where c.prc_date BETWEEN b.prc_date and a.prc_date) <=3
) as onhand_amount
from accounts a;

在这里插入图片描述

查询重叠的区间(带续)

在这里插入图片描述
灰色是有重叠的部分

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)
));