1. 计算同一组或分区的行之间的差

1.1. 最终结果集

1.1.1. sql

DEPTNO ENAME             SAL HIREDATE    DIFF
------ ---------- ---------- ----------- ----------
    10 CLARK            2450 09-JUN-1981      -2550
    10 KING             5000 17-NOV-1981       3700
    10 MILLER           1300 23-JAN-1982        N/A
    20 SMITH             800 17-DEC-1980      -2175
    20 JONES            2975 02-APR-1981        -25
    20 FORD             3000 03-DEC-1981          0
    20 SCOTT            3000 09-DEC-1982       1900
    20 ADAMS            1100 12-JAN-1983        N/A
    30 ALLEN            1600 20-FEB-1981        350
    30 WARD             1250 22-FEB-1981      -1600
    30 BLAKE            2850 01-MAY-1981       1350
    30 TURNER           1500 08-SEP-1981        250
    30 MARTIN           1250 28-SEP-1981        300
    30 JAMES             950 03-DEC-1981        N/A

1.1.2. 每个员工的DEPTNO、ENAME和SAL，以及同一个部门（即DEPTNO相同）里不同员工之间的工资差距

1.1.3. 一个部门里入职日期最晚的那个员工，将其工资差距设置为N/A

1.2. DB2

1.3. PostgreSQL

1.4. MySQL

1.5. SQL Server

1.6. sql

select deptno,ename,hiredate,sal,
         coalesce(cast(sal-next_sal as char(10)),'N/A') as diff
    from (
  select e.deptno,
         e.ename,
         e.hiredate,
         e.sal,
         (select min(sal) from emp d
           where d.deptno=e.deptno
             and d.hiredate =
                  (select min(hiredate) from emp d
                    where e.deptno=d.deptno
                    and d.hiredate ＞ e.hiredate)) as next_sal
    from emp e
         ) x

1.6.2. 使用标量子查询找出同一个部门里紧随当前员工之后入职的员工的HIREDATE

1.6.3. 使用了MIN(HIREDATE)来确保仅返回一个值

1.6.3.1. 即使同一天入职的员工不止一个人，也只会返回一个值

1.6.4. 另一个标量子查询来找出入职日期等于NEXT_HIRE的员工的工资

1.6.4.1. 使用MIN函数来确保只返回一个值

1.7. Oracle

1.7.1.  sql

select deptno,ename,sal,hiredate,
        lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
   from (
 select deptno,ename,sal,hiredate,
        lead(sal)over(partition by deptno
                          order by hiredate) next_sal
   from emp
        )

2. 定位连续值区间的开始值和结束值

2.1. 示例

2.1.1. sql

select *
  from V
PROJ_ID PROJ_START  PROJ_END
------- ----------- -----------
      1 01-JAN-2005 02-JAN-2005
      2 02-JAN-2005 03-JAN-2005
      3 03-JAN-2005 04-JAN-2005
      4 04-JAN-2005 05-JAN-2005
      5 06-JAN-2005 07-JAN-2005
      6 16-JAN-2005 17-JAN-2005
      7 17-JAN-2005 18-JAN-2005
      8 18-JAN-2005 19-JAN-2005
      9 19-JAN-2005 20-JAN-2005
     10 21-JAN-2005 22-JAN-2005
     11 26-JAN-2005 27-JAN-2005
     12 27-JAN-2005 28-JAN-2005
     13 28-JAN-2005 29-JAN-2005
     14 29-JAN-2005 30-JAN-2005

2.2. 最终结果集

2.2.1.  sql

PROJ_GRP PROJ_START  PROJ_END
-------- ----------- -----------
       1 01-JAN-2005 05-JAN-2005
       2 06-JAN-2005 07-JAN-2005
       3 16-JAN-2005 20-JAN-2005
       4 21-JAN-2005 22-JAN-2005
       5 26-JAN-2005 30-JAN-2005

2.2.2. 必须明确什么是区间

2.2.2.1. PROJ_START和PROJ_END的值决定哪些行属于同一个区间

2.2.2.2. 如果某一行的PROJ_START值等于上一行的PROJ_END值，那么该行就是“连续”的，或者说它属于某个组

2.3. DB2

2.4. PostgreSQL

2.5. MySQL

2.6. SQL Server

2.7. sql

create view v2
as
select a.*,
       case
         when (
            select b.proj_id
              from V b
             where a.proj_start = b.proj_end
              )
              is not null then 0 else 1
       end as flag
  from V a

2.7.2.

select proj_grp,
         min(proj_start) as proj_start,
         max(proj_end) as proj_end
    from (
  select a.proj_id,a.proj_start,a.proj_end,
         (select sum(b.flag)
            from V2 b
           where b.proj_id ＜= a.proj_id) as proj_grp
    from V2 a
         ) x
   group by proj_grp

2.8. Oracle

2.8.1.   sql

select proj_grp, min(proj_start), max(proj_end)
    from (
  select proj_id,proj_start,proj_end,
         sum(flag)over(order by proj_id) proj_grp
    from (
  select proj_id,proj_start,proj_end,
         case when
              lag(proj_end)over(order by proj_id) = proj_start
              then 0 else 1
         end flag
    from V
         )
         )
   group by proj_grp

3. 生成连续的数值

3.1. DB2

3.2. SQL Server

3.3. sql

with x (id)
  as (
  select 1
    from t1
   union all
  select id+1
    from x
   where id+1 ＜= 10
  )
  select * from x

3.4. Oracle

3.4.1.  sql

with x
 as (
 select level id
   from dual
   connect by level ＜= 10
 )
 select * from x

3.4.1.1. oracle9i

3.4.1.2. 在WHERE子句中断之前，行数据会被连续生成出来。Oracle会自动递增伪列LEVEL的值

3.4.2.  sql

select array id
   from dual
  model
    dimension by (0 idx)
    measures(1 array)
    rules iterate (10) (
      array[iteration_number] = iteration_number+1
    )

3.4.2.1. oracle10g

3.4.2.2. 在MODEL子句解决方案里，有一个显式的ITERATE命令，该命令帮助生成多行数据

3.5. PostgreSQL

3.5.1.  sql

select id
   from generate_series (1,10) x(id)

3.5.1.1. GENERATE_SERIES函数有3个参数，它们都是数值类型

3.5.1.2. 第一个参数是初始值，第二个参数是结束值，第三个参数是可选项，代表“步长”（每次增加的值）

3.5.1.3. 如果没有指定第3个参数，则默认每次增加1

3.5.1.4. 传递给它的参数甚至可以不是常量

3.5.1.5. sql

select id
  from generate_series(
         (select min(deptno) from emp),
         (select max(deptno) from emp),
         5
       ) x(id)