 # 1. 年月日加減法 ## 1.1. DB2 ### 1.1.1. sql ```sql select hir ...
1. 年月日加減法
1.1. DB2
1.1.1. sql
select hiredate -5 day as hd_minus_5D,
hiredate +5 day as hd_plus_5D,
hiredate -5 month as hd_minus_5M,
hiredate +5 month as hd_plus_5M,
hiredate -5 year as hd_minus_5Y,
hiredate +5 year as hd_plus_5Y
from emp
where deptno = 10
1.2. Oracle
1.2.1. sql
select hiredate-5 as hd_minus_5D,
hiredate+5 as hd_plus_5D,
add_months(hiredate,-5) as hd_minus_5M,
add_months(hiredate,5) as hd_plus_5M,
add_months(hiredate,-5*12) as hd_minus_5Y,
add_months(hiredate,5*12) as hd_plus_5Y
from emp
where deptno = 10
1.3. PostgreSQL
1.3.1. sql
select hiredate - interval '5 day' as hd_minus_5D,
hiredate + interval '5 day' as hd_plus_5D,
hiredate - interval '5 month' as hd_minus_5M,
hiredate + interval '5 month' as hd_plus_5M,
hiredate - interval '5 year' as hd_minus_5Y,
hiredate + interval '5 year' as hd_plus_5Y
from emp
where deptno=10
1.4. MySQL
1.4.1. sql
select hiredate - interval 5 day as hd_minus_5D,
hiredate + interval 5 day as hd_plus_5D,
hiredate - interval 5 month as hd_minus_5M,
hiredate + interval 5 month as hd_plus_5M,
hiredate - interval 5 year as hd_minus_5Y,
hiredate + interval 5 year as hd_plus_5Y
from emp
where deptno=10
1.4.2. sql
select date_add(hiredate,interval -5 day) as hd_minus_5D,
date_add(hiredate,interval 5 day) as hd_plus_5D,
date_add(hiredate,interval -5 month) as hd_minus_5M,
date_add(hiredate,interval 5 month) as hd_plus_5M,
date_add(hiredate,interval -5 year) as hd_minus_5Y,
date_add(hiredate,interval 5 year) as hd_plus_5DY
from emp
where deptno=10
1.5. SQL Server
1.5.1. sql
select dateadd(day,-5,hiredate) as hd_minus_5D,
dateadd(day,5,hiredate) as hd_plus_5D,
dateadd(month,-5,hiredate) as hd_minus_5M,
dateadd(month,5,hiredate) as hd_plus_5M,
dateadd(year,-5,hiredate) as hd_minus_5Y,
dateadd(year,5,hiredate) as hd_plus_5Y
from emp
where deptno = 10
1.6. SQL 的ISO 標準語法里規定了INTERVAL關鍵字以及緊隨其後的字元串常量
1.6.1. 該標準要求INTERVAL值必須位於英文單引號內
1.6.2. PostgreSQL ( 和Oracle 9i資料庫及其後續版本 ) 遵循了該標準
1.6.3. MySQL 則不支持英文單引號，略微偏離了標準
2. 兩個日期之間的天數
2.1. 內嵌視圖X和Y被用於分別獲取WARD 和ALLEN 的HIREDATE
2.1.1. sql
select ward_hd, allen_hd
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) y,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) x
WARD_HD ALLEN_HD
----------- ---------
22-FEB-1981 20-FEB-1981
2.1.1.1. 因為X和Y之間沒有任何連接條件，這裡會產生笛卡兒積
2.1.1.2. X和Y都只有一條數據，因而即使沒有連接條件也不會有問題，結果集最終只會有一行
2.2. DB2
2.2.1. sql
select days(ward_hd) - days(allen_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.3. Oracle
2.4. PostgreSQL
2.5. sql
select ward_hd - allen_hd
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.6. MySQL
2.7. SQL Server
2.8. sql
select datediff(day,allen_hd,ward_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.8.1.1. 對於MySQL 而言，只需去掉DATEDIFF函數的第一個參數，並翻轉ALLEN_HD和WARD_HD的順序即可
3. 兩個日期之間的工作日天數
3.1. 思路
3.1.1. 計算出開始日期和結束日期之間相隔多少天（包含開始日期和結束日期）
3.1.2. 排除掉周末，統計有多少個工作日（實際是在計算有多少條記錄）
3.1.2.1. sql
select case when ename = 'BLAKE'
then hiredate
end as blake_hd,
case when ename = 'JONES'
then hiredate
end as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
02-APR-1981
01-MAY-1981
3.1.2.2. sql
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
01-MAY-1981 02-APR-1981
3.1.2.2.1. 使用了聚合函數MAX，其目的在於排除掉Null
3.1.3. T500表的ID列每一個值都等於前面一行的值加上1
3.1.3.1. sql
select x.*, t500.*, jones_hd+t500.id-1
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
BLAKE_HD JONES_HD ID JONES_HD+T5
----------- ----------- ---------- -----------
01-MAY-1981 02-APR-1981 1 02-APR-1981
01-MAY-1981 02-APR-1981 2 03-APR-1981
01-MAY-1981 02-APR-1981 3 04-APR-1981
01-MAY-1981 02-APR-1981 4 05-APR-1981
01-MAY-1981 02-APR-1981 5 06-APR-1981
01-MAY-1981 02-APR-1981 6 07-APR-1981
01-MAY-1981 02-APR-1981 7 08-APR-1981
01-MAY-1981 02-APR-1981 8 09-APR-1981
01-MAY-1981 02-APR-1981 9 10-APR-1981
01-MAY-1981 02-APR-1981 10 11-APR-1981
01-MAY-1981 02-APR-1981 11 12-APR-1981
01-MAY-1981 02-APR-1981 12 13-APR-1981
01-MAY-1981 02-APR-1981 13 14-APR-1981
01-MAY-1981 02-APR-1981 14 15-APR-1981
01-MAY-1981 02-APR-1981 15 16-APR-1981
01-MAY-1981 02-APR-1981 16 17-APR-1981
01-MAY-1981 02-APR-1981 17 18-APR-1981
01-MAY-1981 02-APR-1981 18 19-APR-1981
01-MAY-1981 02-APR-1981 19 20-APR-1981
01-MAY-1981 02-APR-1981 20 21-APR-1981
01-MAY-1981 02-APR-1981 21 22-APR-1981
01-MAY-1981 02-APR-1981 22 23-APR-1981
01-MAY-1981 02-APR-1981 23 24-APR-1981
01-MAY-1981 02-APR-1981 24 25-APR-1981
01-MAY-1981 02-APR-1981 25 26-APR-1981
01-MAY-1981 02-APR-1981 26 27-APR-1981
01-MAY-1981 02-APR-1981 27 28-APR-1981
01-MAY-1981 02-APR-1981 28 29-APR-1981
01-MAY-1981 02-APR-1981 29 30-APR-1981
01-MAY-1981 02-APR-1981 30 01-MAY-1981
3.1.3.1.1. Oracle語法
3.1.3.1.2. 一旦生成了所需數目的行記錄，接著使用CASE表達式來標記每一個日期是工作日或者周末（若是工作日返回1，周末則返回0）
3.1.3.1.3. 使用聚合函數SUM來合計1的個數，並得到最終答案
3.2. DB2
3.2.1. sql
select sum(case when dayname(jones_hd+t500.id day -1 day)
in ( 'Saturday','Sunday' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.2.1.1. WHERE子句的話，BLAKE_HD和JONES_HD相減後又加上了1
3.2.1.2. SELECT列表裡T500.ID減去了1，這是因為ID列的起始值是1，如果在JONES_HD基礎上加上1就等同於從最終結果里排除掉了JONES_HD
3.3. Oracle
3.3.1. sql
select sum(case when to_char(jones_hd+t500.id-1,'DY')
in ( 'SAT','SUN' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.4. PostgreSQL
3.4.1. sql
select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))
in ( 'SATURDAY','SUNDAY' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.5. MySQL
3.5.1. sql
select sum(case when date_format(
date_add(jones_hd,
interval t500.id-1 DAY),'%a')
in ( 'Sat','Sun' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= datediff(blake_hd,jones_hd)+1
3.6. SQL Server
3.6.1. sql
select sum(case when datename(dw,jones_hd+t500.id-1)
in ( 'SATURDAY','SUNDAY' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= datediff(day,jones_hd-blake_hd)+1
