文章來源地址http://www.zghlxwxcb.cn/news/detail-548331.html
1.?兩個日期之間相差的月份和年份
1.1.?DB2
1.2.?MySQL
1.3.?sql
select mnth, mnth/12
from ( select (year(max_hd) - year(min_hd))*12 +
(month(max_hd) - month(min_hd)) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y
1.4.?Oracle
1.4.1.??sql
select months_between(max_hd,min_hd),
months_between(max_hd,min_hd)/12
from (
select min(hiredate) min_hd, max(hiredate) max_hd
from emp
) x
1.5.?PostgreSQL
1.5.1.???sql
select mnth, mnth/12
from (
select ( extract(year from max_hd) -
extract(year from min_hd) ) * 12
+
( extract(month from max_hd) -
extract(month from min_hd) ) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y
1.6.?SQL Server
1.6.1.???sql
select datediff(month,min_hd,max_hd),
datediff(month,min_hd,max_hd)/12
from (
select min(hiredate) min_hd, max(hiredate) max_hd
from emp
) x
2.?兩個日期之間相差的秒數(shù)、分鐘數(shù)和小時數(shù)
2.1.?相差的天數(shù)分別乘以24（一天的小時數(shù)），1440（一天的分鐘數(shù)）和86400（一天的秒數(shù)）
2.2.?DB2
2.2.1.???sql
select dy*24 hr, dy*24*60 min, dy*24*60*60 sec
from (
select ( days(max(case when ename = 'WARD'
then hiredate
end)) -
days(max(case when ename = 'ALLEN'
then hiredate
end))
) as dy
from emp
) x
2.3.?Oracle
2.4.?PostgreSQL
2.5.?sql
select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec
from (
select (max(case when ename = 'WARD'
then hiredate
end) -
max(case when ename = 'ALLEN'
then hiredate
end)) as dy
from emp
) x
2.6.?MySQL
2.7.?SQL Server
2.8.?sql
select datediff(day,allen_hd,ward_hd)*24 hr,
datediff(day,allen_hd,ward_hd)*24*60 min,
datediff(day,allen_hd,ward_hd)*24*60*60 sec
from (
select max(case when ename = 'WARD'
then hiredate
end) as ward_hd,
max(case when ename = 'ALLEN'
then hiredate
end) as allen_hd
from emp
) x
3.?當(dāng)前記錄和下一條記錄之間的日期差
3.1.?DB2
3.1.1.??sql
select x.*,
days(x.next_hd) - days(x.hiredate) diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) next_hd
from emp e
where e.deptno = 10
) x
3.2.?Oracle
3.2.1.???sql
select ename, hiredate, next_hd,
next_hd - hiredate diff
from (
select deptno, ename, hiredate,
lead(hiredate)over(order by hiredate) next_hd
from emp
)
where deptno=10
3.3.?PostgreSQL
3.3.1.??sql
select x.*,
x.next_hd - x.hiredate as diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) as next_hd
from emp e
where e.deptno = 10
) x
3.4.?MySQL
3.5.?SQL Server
3.6.?sql
select x.*,
datediff(day,x.hiredate,x.next_hd) diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) next_hd
from emp e
where e.deptno = 10
) x
3.6.2.??datediff(x.next_hd, x.hiredate) diff
3.6.2.1.?對于MySQL 版本的DATEDIFF函數(shù)，需要省略第一個參數(shù)day，并把剩下的兩個參數(shù)的順序顛倒過來
4.?一年中有多少個星期一
4.1.?方案
4.1.1.?生成一年里所有可能的日期值
4.1.2.?格式化上述日期值，并找出它們分別是星期幾
4.1.3.?統(tǒng)計每個“星期x”出現(xiàn)的次數(shù)
4.2.?DB2
4.2.1.???sql
with x (start_date,end_date)
as (
select start_date,
start_date + 1 year end_date
from (
select (current_date -
dayofyear(current_date) day)
+1 day as start_date
from t1
)tmp
union all
select start_date + 1 day, end_date
from x
where start_date + 1 day ＜ end_date
)
select dayname(start_date),count(*)
from x
group by dayname(start_date)
4.3.?Oracle
4.3.1.??sql
with x as (
select level lvl
from dual
connect by level ＜= (
add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)
)
select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
from x
group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')
4.3.2.??sql
select to_char(trunc(sysdate,'y')+rownum-1,'DAY'),
count(*)
from t500
where rownum ＜= (add_months(trunc(sysdate,'y'),12)
- trunc(sysdate,'y'))
group by to_char(trunc(sysdate,'y')+rownum-1,'DAY')
4.3.2.1.?Oracle早期版本
4.4.?PostgreSQL
4.4.1.???sql
select to_char(
cast(
date_trunc('year',current_date)
as date) + gs.id-1,'DAY'),
count(*)
from generate_series(1,366) gs(id)
where gs.id ＜= (cast
( date_trunc('year',current_date) +
interval '12 month' as date) -
cast(date_trunc('year',current_date)
as date))
group by to_char(
cast(
date_trunc('year',current_date)
as date) + gs.id-1,'DAY')
4.5.?MySQL
4.5.1.???sql
select date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W') day,
count(*)
from t500
where t500.id ＜= datediff(
cast(
concat(year(current_date)+1,'-01-01')
as date),
cast(
concat(year(current_date),'-01-01')
as date))
group by date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W')
4.6.?SQL Server
4.6.1.???sql
with x (start_date,end_date)
as (
select start_date,
dateadd(year,1,start_date) end_date
from (
select cast(
cast(year(getdate()) as varchar) + '-01-01'
as datetime) start_date
from t1
) tmp
union all
select dateadd(day,1,start_date), end_date
from x
where dateadd(day,1,start_date) ＜ end_date
)
select datename(dw,start_date),count(*)
from x
group by datename(dw,start_date)
OPTION (MAXRECURSION 366)
文章來源：http://www.zghlxwxcb.cn/news/detail-548331.html
到了這里，關(guān)于選讀SQL經(jīng)典實例筆記05_日期運算（下）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
