用关联子查询比较行与行

1.统计出下表的增长，减少和维持不变

Sales.png

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 LEFT JOIN Sales S2
ON S2.year = S1.year - 1
 ORDER BY year;

或者

SELECT S1.year, S1.sale,
       CASE SIGN(sale -
              (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1) )
            WHEN 0  THEN '→'  /* 持平 */
            WHEN 1  THEN '↑'  /* 增长   */
            WHEN -1 THEN '↓'  /* 减少   */
            ELSE '—' END AS var
  FROM Sales S1
 ORDER BY year;

不论是关联子查询还是什么join都是通过条件和下一行数据比较。

2.统计有年份缺失的表

Sales2.png

--- 多看几遍，加深理解，没有想出来
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;

自连接的实现，ON条件多理解几次。之前总是想用S1和S2实现，但是没有实现成功，这里S1用来显示今年，S2显示上一个有统计的年份，因为这里并不是总是差一年的关系，所以用S3选择出最接近的年份。

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 LEFT OUTER JOIN Sales2 S2
    ON S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

3.移动累计值和移动平均值

Accounts.png

求每个日期对应的余额

SELECT prc_date,(SELECT SUM(prc_amt) from Accounts t where t.prc_date <= Accounts.prc_date) prc_amt FROM Accounts 

image.png

上面的是从一开始累计计算的，下面的要求以3次为单位求累计值

SELECT prc_date,a1.prc_amt,(SELECT SUM(prc_amt) from Accounts a2 where a2.prc_date <= a1.prc_date) sum_amt,
 (SELECT AVG(a3.prc_amt) FROM Accounts a3 WHERE a1.prc_date >= a3.prc_date and (SELECT COUNT(*) FROM Accounts a4 WHERE a4.prc_date BETWEEN a3.prc_date and a1.prc_date) <=3 HAVING  COUNT(*) =3) avg_amt
FROM Accounts a1

这里的HAVING条件用来设置不足3行的可以按照原来的输出。

image.png

4.查询重叠的时间区间

Reservations.png

SELECT R1.reserver,R1.start_date,R1.end_date FROM Reservations R1 
WHERE  (SELECT COUNT(*) FROM Reservations R2
 WHERE (R2.start_date BETWEEN R1.start_date AND 
R1.end_date
OR  R2.end_date BETWEEN R1.start_date AND 
R1.end_date) and --  R1.reserver != R2.reserver 加上这个就出不来了，以后想想
) > 1 ORDER BY R1.start_date asc

用EXIST关键字

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
                             -- 条件(1)：自己的入住日期在他人的住宿期间内
            OR R1.end_date  BETWEEN R2.start_date AND R2.end_date));
                             -- 条件(2)：自己的离店日期在他人的住宿期间内