SQL进阶--用关联子查询比较行与行

1、增长、减少、维持现状

-- 建表
drop table if exists sales;
create table sales(
year1 year,
sale int8
);
insert into sales values(1990, 50);
insert into sales values(1991, 51);
insert into sales values(1992, 52);
insert into sales values(1993, 52);
insert into sales values(1994, 50);
insert into sales values(1995, 50);
insert into sales values(1996, 49);
insert into sales values(1997, 55);

展示与上一年的比较

select a.year1, a.sale,
       case when a.sale = b.sale then '→'  -- 持平
            when a.sale > b.sale then '↑'  -- 增长
            when a.sale < b.sale then '↓'  -- 减少
       else '-' end as var
  from sales a left join sales b
    on b.year1 = a.year1 - 1  -- 为了将没有可比较年份也展示出来
 order by year1
;

查询每一年与过去最近的年份之间的营业额之差

select b.year1 as pre_year,
       a.year1 as now_year,
       a.sale  as pre_sale,
       b.sale  as now_sale,
       a.sale - b.sale as diff
  from sales a left join sales b  
    on b.year1 = (select max(year1) from sales c where a.year1 > c.year1)  -- 非等值连接
 order by now_year;

2、移动累计值

-- 建表
drop table if exists accounts;
create table accounts(
prc_date date,
prc_amt int8
);
insert into accounts values(20061026,12000);
insert into accounts values(20061028,2500);
insert into accounts values(20061031,-15000);
insert into accounts values(20061103,34000);
insert into accounts values(20061104,-5000);
insert into accounts values(20061106,7200);
insert into accounts values(20061111,11000);

求累计值：使用冯。诺依曼型递归集合

select prc_date, a1.prc_amt,
       (select sum(prc_amt)    -- 在求秩的时候用是count()
          from accounts a2
         where a1.prc_date >= a2.prc_date) as onhand_amt
  from accounts a1
 order by prc_date;

3、查询重叠的时间区间

思路：
（1）自己入住日期在他人住宿期间内
（2）自己离店日期在他人住宿期间内
（3）自己的入住日期和离店时间都在他人住宿期间内

-- 建表
drop table if exists accounts;
create table accounts(
prc_date date,
prc_amt int8
);
insert into accounts values(20061026,12000);
insert into accounts values(20061028,2500);
insert into accounts values(20061031,-15000);
insert into accounts values(20061103,34000);
insert into accounts values(20061104,-5000);
insert into accounts values(20061106,7200);
insert into accounts values(20061111,11000);

查询重叠住宿

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

练习

求累计平均

select prc_date, a1.prc_amt,
       (select round(avg(prc_amt),2)
          from accounts a2
         where a1.prc_date >= a2.prc_date) as onhand_min  -- 求累计平均
  from accounts a1
 order by prc_date
 ;

内容多来自 《SQL进阶教材》，仅做笔记。练习部分代码均为原创。