MySQL-（数据过滤）连接查询的各种应用

下面这张记录了某个公司每年的营业额的表 tSales：

tyear（年份） sale（年营业额 ：亿日元）

1990               50

1991               51

1992               52

1993               52

1994               50

1995               50

1996               49

1997               55

我们先创建表，并插入数据

1.先创建tSales表；

CREATE TABLE tSales(
 tyear YEAR,
 sale INT(10)
 );

2.给tSales表插入数据；

INSERT INTO tSales VALUES
(1990,50),
(1991,51),
(1992,52),
(1993,52),
(1994,50),
(1995,50),
(1996,49),
(1997,55);

3.查询数据插入情况；

SELECT * FROM tSales;

接下来，依据需求实现。

 求与上一年营业额一样的年份 (1)：使用关联子查询 

SELECT tyear,sale  
FROM tSales S1 
WHERE 
sale = (SELECT sale FROM tSales S2 WHERE S2.tyear = S1.tyear - 1) 
ORDER BY tyear;

子查询里的 S2.tyear = S1.tyear – 1 这个条件起到了将要比较的数据偏移一行的作用。关联子查询和自连接在很多时候都是等价的，所以我们也可以像下面这样使用自连接来实现。
求与上一年营业额一样的年份 (2)：使用自连接 

SELECT S1.tyear, S1.sale
FROM tSales S1,tSales S2 
WHERE S2.sale = S1.sale AND S2.tyear = S1.tyear - 1 
ORDER BY S1.tyear;

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

求出是增长了还是减少了，抑或是维持现状 (1)：使用关联子查询 

SELECT S1.tyear, S1.sale,
CASE WHEN sale = (SELECT sale FROM tSales S2 WHERE S2.tyear = S1.tyear - 1) THEN '→' -- 持平
     WHEN sale > (SELECT sale FROM tSales S2 WHERE S2.tyear = S1.tyear - 1) THEN '↑' -- 增长
     WHEN sale < (SELECT sale FROM tSales S2 WHERE S2.tyear = S1.tyear - 1) THEN '↓' -- 减少
     ELSE '—' END AS var FROM tSales S1 
ORDER BY tyear;

求出是增长了还是减少了，抑或是维持现状(2)：使用自连接查询（最早的年份不会出现在结果里） 

SELECT S1.tyear, 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 tSales S1, tSales S2 
WHERE S2.tyear = S1.tyear - 1 
ORDER BY tyear;

采用这种实现方法时，由于这里没有1990 年之前的数据，所以1990 年会被排除掉，执行结果会少一行。虽然没有这一行也不至于产生大的问题，但是有时候我们也会遇到“最早的年份也要包含在结果里”这样的需求。

对于“公司丢失了过去个别年份的数据”，需要另外对待：

我们来模拟，删除几个数据

DELETE FROM tSales WHERE tyear="1991" OR tyear="1996";

或者

DELETE FROM tSales WHERE tyear IN (1991,1996);

并插入一个

INSERT INTO tSales VALUES
(1998,55);

查询SELECT * FROM tSales;

对某一年来说，“过去最临近的年份”需要满足下面两个条件。
1. 与该年份相比是过去的年份。 2. 在满足条件 1 的年份中，年份最早的一个。
如果按这两个条件改写 SQL 语句，那么应该像下面这样写。

SELECT S1.tyear,S1.sale FROM tSales S1 
WHERE S1.sale =
(
SELECT S2.sale FROM tSales S2 
WHERE S2.tyear =(
SELECT MAX(S3.tyear) FROM tSales S3
WHERE S1.tyear > S3.tyear)
);

未完，待续...