翼度科技»论坛 编程开发 mysql 查看内容

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

5

主题

5

帖子

15

积分

新手上路

Rank: 1

积分
15

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)
复制代码
来源:https://www.cnblogs.com/lying7/p/17559800.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具