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

sales表



求第二年相对于第一年是增加了还是减少了

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 var
from sales s1,sales s2
where 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;

2. 时间轴有间断时：和过去最邻近的世界进行比较

sales2表



s1:今年，s2：去年，s3：小于今年的最大的

select s2.year pre_year,s1.year now_year,s2.sale pre_sale,s1.sale now_sale,
case when s1.sale<s2.sale then '↑' 
 when s1.sale=s2.sale then '→' 
 when s1.sale>s2.sale then '↓' else '-' end var
from sales2 s1,sales2 s2
where s2.year=(select max(s3.year) from sales2 s3 where s3.year<s1.year)
order by s1.year;

如果需要显示第一年的，可以用连接

select s2.year pre_year,s1.year now_year,s2.sale pre_sale,s1.sale now_sale,
case when s1.sale<s2.sale then '↑' 
 when s1.sale=s2.sale then '→' 
 when s1.sale>s2.sale then '↓' else '-' end var
from sales2 s1 left join sales2 s2 
on s2.year=(select max(s3.year) from sales2 s3 where s3.year<s1.year)
order by s1.year;

3. 移动累计值

account表



计算每年到第一年prc_amt累计改变多少

select prc_date,prc_amt,
(select sum(prc_amt) from accounts a2 where a2.prc_date<=a1.prc_date) onhand_amt
from accounts a1 order by prc_date;

以3年为窗口处理

select prc_date,prc_amt,
(select sum(prc_amt) from accounts a2 where a1.prc_date>=a2.prc_date
and (select count(*) from accounts a3 where a3.prc_date between a2.prc_date and a1.prc_date)<=3) onhand_amt
from accounts a1 order by prc_date;

注意between a2.prc_date and a1.prc_date，一定是先小再大

4.移动平均

将3中的sum函数换成avg就可以了

5. 查询重叠的时间区间

reservations表



这是某个房间的预约表，查询出存在重叠的预定日期的信息。

需要出现：

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
or r1.end_date between r2.start_date and r2.end_date
or (r2.start_date between r1.start_date and r1.end_date
and r2.end_date between r1.start_date and r1.end_date
))
);