选读SQL经典实例笔记05_日期运算(下)
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)
6.选读SQL经典实例笔记05_日期运算(下)
7.选读SQL经典实例笔记06_日期处理(上)8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询12.选读SQL经典实例笔记11_结果集变换13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合15.选读SQL经典实例笔记14_层次查询16.选读SQL经典实例笔记15_窗口函数17.选读SQL经典实例笔记16_逻辑否定18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All21.选读SQL经典实例笔记20_Oracle语法示例22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补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. 两个日期之间相差的秒数、分钟数和小时数
2.1. 相差的天数分别乘以24(一天的小时数),1440(一天的分钟数)和86400(一天的秒数)
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. 当前记录和下一条记录之间的日期差
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函数,需要省略第一个参数day,并把剩下的两个参数的顺序颠倒过来
4. 一年中有多少个星期一
4.1. 方案
4.1.1. 生成一年里所有可能的日期值
4.1.2. 格式化上述日期值,并找出它们分别是星期几
4.1.3. 统计每个“星期x”出现的次数
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)
合集:
选读SQL经典实例
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业