选读SQL经典实例笔记08_区间查询
2023-07-17 本文已影响0人
躺柒
选读SQL经典实例笔记08_区间查询.png
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)
