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

查询与过去最临近的年份营业额相同的年份 ：同时使用自连接
SELECT 
S1.tyear AS tyear,
S2.tyear AS tyear 
FROM tSales S1,tSales S2 
WHERE S1.sale = S2.sale AND 
S2.tyear = (
SELECT MAX(S3.tyear)
FROM tSales S3
WHERE S1.tyear > S3.tyear) 
ORDER BY S1.tyear;

 求每一年与过去最临近的年份之间的营业额之差 (1)：结果里不包含最早的年份 

SELECT 
S2.tyear AS pre_year,
S1.tyear AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff 
FROM tSales S1,tSales S2 
WHERE S2.tyear = (
SELECT MAX(S3.tyear) 
FROM tSales S3 
WHERE S1.tyear > S3.tyear) 
ORDER BY now_year;

从执行结果可以发现，这条 SQL 语句无法获取到最早年份1990 年的 数据。这是因为，表里没有比1990 年更早的年份，所以在进行内连接的 时候1990 年的数据就被排除掉了。如果想让结果里出现1990 年的数据， 可以使用“自外连接”来实现。

求每一年与过去最临近的年份之间的营业额之差 (2)：使用自外连接。结果里包含最早的年份 

SELECT 
S2.tyear AS pre_year,
S1.tyear AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff 
FROM tSales S1 LEFT OUTER JOIN tSales S2
ON S2.tyear = (
SELECT MAX(S3.tyear) 
  FROM tSales S3 
  WHERE S1.tyear>S3.tyear) 
ORDER BY now_year;

将表tSales作为主表进行外连接后，所有年份就都出现在表侧栏里了。 

这条 SQL 语句能与过去与其（这里指某一年）最近的年份进行比较， 因此即使年份有缺失也没有关系。而且，不只是数值，这条SQL 语句还可以应用于字符类型、日期类型等具有顺序的列，通用性比较高。但是，因为使用极值函数时会发生排序，所以与上一部分中的实现方法相比，这个方法在性能方面稍微逊色（如果极值函数的参数是主键，有时也是可以使用索引的）。
 

未完，待续...