 # 1. 計算同一組或分區的行之間的差 ## 1.1. 最終結果集 ### 1.1.1. sql ```sql D ...
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)
