选读SQL经典实例笔记08_区间查询

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

1.1. 最终结果集

1.1.1. sql

1. DEPTNO ENAME             SAL HIREDATE    DIFF
2. ------ ---------- ---------- ----------- ----------
3.     10 CLARK            2450 09-JUN-1981      -2550
4.     10 KING             5000 17-NOV-1981       3700
5.     10 MILLER           1300 23-JAN-1982        N/A
6.     20 SMITH             800 17-DEC-1980      -2175
7.     20 JONES            2975 02-APR-1981        -25
8.     20 FORD             3000 03-DEC-1981          0
9.     20 SCOTT            3000 09-DEC-1982       1900
10.     20 ADAMS            1100 12-JAN-1983        N/A
11.     30 ALLEN            1600 20-FEB-1981        350
12.     30 WARD             1250 22-FEB-1981      -1600
13.     30 BLAKE            2850 01-MAY-1981       1350
14.     30 TURNER           1500 08-SEP-1981        250
15.     30 MARTIN           1250 28-SEP-1981        300
16.     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

1. select deptno,ename,hiredate,sal,
2.          coalesce(cast(sal-next_sal as char(10)),'N/A') as diff
3.     from (
4.   select e.deptno,
5.          e.ename,
6.          e.hiredate,
7.          e.sal,
8.          (select min(sal) from emp d
9.            where d.deptno=e.deptno
10.              and d.hiredate =
11.                   (select min(hiredate) from emp d
12.                     where e.deptno=d.deptno
13.                     and d.hiredate ＞ e.hiredate)) as next_sal
14.     from emp e
15.          ) 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

1. select deptno,ename,sal,hiredate,
2.         lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
3.    from (
4. select deptno,ename,sal,hiredate,
5.         lead(sal)over(partition by deptno
6.                           order by hiredate) next_sal
7.    from emp
8.         )

复制代码

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

2.1. 示例

2.1.1. sql

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

复制代码

2.2. 最终结果集

2.2.1.  sql

1. PROJ_GRP PROJ_START  PROJ_END
2. -------- ----------- -----------
3.        1 01-JAN-2005 05-JAN-2005
4.        2 06-JAN-2005 07-JAN-2005
5.        3 16-JAN-2005 20-JAN-2005
6.        4 21-JAN-2005 22-JAN-2005
7.        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

1. create view v2
2. as
3. select a.*,
4.        case
5.          when (
6.             select b.proj_id
7.               from V b
8.              where a.proj_start = b.proj_end
9.               )
10.               is not null then 0 else 1
11.        end as flag
12.   from V a

复制代码

2.7.2.

1. select proj_grp,
2.          min(proj_start) as proj_start,
3.          max(proj_end) as proj_end
4.     from (
5.   select a.proj_id,a.proj_start,a.proj_end,
6.          (select sum(b.flag)
7.             from V2 b
8.            where b.proj_id ＜= a.proj_id) as proj_grp
9.     from V2 a
10.          ) x
11.    group by proj_grp

复制代码

2.8. Oracle

2.8.1.   sql

1. select proj_grp, min(proj_start), max(proj_end)
2.     from (
3.   select proj_id,proj_start,proj_end,
4.          sum(flag)over(order by proj_id) proj_grp
5.     from (
6.   select proj_id,proj_start,proj_end,
7.          case when
8.               lag(proj_end)over(order by proj_id) = proj_start
9.               then 0 else 1
10.          end flag
11.     from V
12.          )
13.          )
14.    group by proj_grp

复制代码

3. 生成连续的数值

3.1. DB2

3.2. SQL Server

3.3. sql

1. with x (id)
2.   as (
3.   select 1
4.     from t1
5.    union all
6.   select id+1
7.     from x
8.    where id+1 ＜= 10
9.   )
10.   select * from x

复制代码

3.4. Oracle

3.4.1.  sql

1. with x
2. as (
3. select level id
4.    from dual
5.    connect by level ＜= 10
6. )
7. select * from x

复制代码

3.4.1.1. oracle9i

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

3.4.2.  sql

1. select array id
2.    from dual
3.   model
4.     dimension by (0 idx)
5.     measures(1 array)
6.     rules iterate (10) (
7.       array[iteration_number] = iteration_number+1
8.     )

复制代码

3.4.2.1. oracle10g

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

3.5. PostgreSQL

3.5.1.  sql

1. select id
2.    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

1. select id
2.   from generate_series(
3.          (select min(deptno) from emp),
4.          (select max(deptno) from emp),
5.          5
6.        ) x(id)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！