SQL进阶之路06：关联子查询

其实，在平日数据库使用中对同一行数据进列的比较很简单，只需要在WHERE添加条件即可！但是如果对于不同行数据的数据进行比较可能就没有那么简单快捷了。这不是说不可以进行与行之间的比较，而是说比较麻烦。这次讲解关联子查询或许可以改变你想法。

例题：假设一张表记录了每年的销售额。

 如何比较输出与上一年相比是增加或者减少的数据？

        首先可以尝试获取到与上一年销售额一致的是数据！

-- 关联子查询：
select s1.* from sales s1 where sale = (select sale from sales s2 where s1.year=s2.year-1)
 
-- 外连接查询：
select s1.* from sales s1 left join sales s2 on s1.sale = s2.sale where s1.year != s2.year

得到答案后进行一个处理后就可以拿到我们需要的结果！

select s1.year , s1.sale , 
CASE WHEN s1.sale > (select sale from sales s2 where s1.year -1 = s2.year ) then "上涨"
		 WHEN s1.sale < (select sale from sales s2 where s1.year -1 = s2.year ) then  "下降"
		 ELSE "持平" END as var 
		 from sales s1 order by s1.year
-- 或
 
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;

但上面写的存在一个问题就当年份一旦出现空挡期那么就会造成数据错误！可以尝试使用比year小但是在小的当中最大值来进行处理。

-- 自连接
select s1.year,s1.sale from sales s1 
where s1.sale = (
		select s2.sale as sale  from sales s2 where s2.year = (
		select max(s3.year)as year from sales s3 where s1.year > s3.year)
)
 
-- 连接查询
select  s1.year,s1.sale from sales s1 left  join sales s2 on s1.sale = s2.sale  
where s2.year = (select max(s3.year) from sales s3 where s1.year > s3.year)

输出想要的结果可以使用CASE来进行

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 AS var
FROM Sales S1
ORDER BY year;