|
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)
复制代码 来源:https://www.cnblogs.com/lying7/p/17559800.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|