【sql查询数据增长下降趋势】

@sql查询数据增长下降趋势

一、示例：比较同去年相比是增加还是下降

1.自连接完成

SELECT S1.year, S1.sale, 
	 CASE WHEN S1.sale= S2.sa1e THEM '→'  
	 			WHEN S1.sale >S2.sa1e THEN '←'  
	 			WHEN S1.sale < S2.sa1e THEN '↓' 
	 			ELSE '--' END  AS var  
FROM Sales S1,Sales S2 
WHERE S2.year=S1.year -1 
ORDER BY year; 

2.关联子查询完成

--求出是增长了还是减少了，抑或是维持现状 ( 1 )： 使用关联子查询
--求出是增长了还是减少了，抑或是维持现状 ( 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 AS var 
FROM
	Sales S1 
ORDER BY
	YEAR;

3.结果

二、示例： 上例中的特殊情况——年份缺失，与最近年对比趋势

1. 关联子查询

--求每一年与过去最临近的年份之间的营业额之差 ( 1 )：结果里不包含 最早的年份
SELECT
	S2.year AS pre_year,
	S1.year AS now_year,
	S2.sale AS pre_sale,
	S1.sale AS now_sale,
	S1.sale - S2.sale AS diff 
FROM
	Sales2 S1,
	Sales2 S2 
WHERE
	S2.year=( SELECT MAX ( YEAR ) FROM Sales2 S3 WHERE S1.year > S3.year ) 
ORDER BY
	now_year;