2022-02-17
求教第二行的入院时间减去第一行的出院时间SQL怎样写？
10M
如图都是同一个人,我想计算一个时间差，第二行的入院时间减去第一行的出院时间，第三行的入院时间减去第二行的出院时间，以此类推。
请教这种SQL能实现吗？
我来答
添加附件
收藏
分享
问题补充
4条回答
默认
最新
楼上采纳的那个有点问题,这个表里肯定不止一个人,所以开窗函数里要加上 “partition by 姓名”,而且也不需要用"ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING"这样的移动窗口,按时间order by 就够了
评论
有用 1
用窗口函数实现。我举一个mysql例。
with cte1 as
(
select 1 as id, '张三' as sname, '2022-01-01' as bdate,'2022-01-03' as edate
union all
select 2 as id,'张三' as sname, '2022-01-08' as bdate,'2022-01-10' as edate
union all
select 3 as id,'张三' as sname, '2022-01-17' as bdate,'2022-01-21' as edate
union all
select 4 as id,'张三' as sname, '2022-01-24' as bdate,'2022-01-25' as edate
union all
select 5 as id,'张三' as sname, '2022-01-27' as bdate,'2022-01-29' as edate
union all
select 6 as id,'张三' as sname, '2022-02-03' as bdate,'2022-02-10' as edate
union all
select 7 as id, '张三' as sname, '2022-02-15' as bdate,'2022-02-23' as edate
)
select id,sname,bdate,edate, nextbdate,datediff(nextbdate,edate) as days from
(
select *,last_value(bdate) over(order by id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as nextbdate
from cte1
) as t1
执行结果：
用lead函数也可以
with cte1 as
(
select 1 as id, '张三' as sname, '2022-01-01' as bdate,'2022-01-03' as edate
union all
select 2 as id,'张三' as sname, '2022-01-08' as bdate,'2022-01-10' as edate
union all
select 3 as id,'张三' as sname, '2022-01-17' as bdate,'2022-01-21' as edate
union all
select 4 as id,'张三' as sname, '2022-01-24' as bdate,'2022-01-25' as edate
union all
select 5 as id,'张三' as sname, '2022-01-27' as bdate,'2022-01-29' as edate
union all
select 6 as id,'张三' as sname, '2022-02-03' as bdate,'2022-02-10' as edate
union all
select 7 as id, '张三' as sname, '2022-02-15' as bdate,'2022-02-23' as edate
)
select id,sname,bdate,edate, datediff(nextbdate,edate) as days from
(
select *,lead(bdate) over(order by id) as nextbdate
from cte1
) as t1
结果和上面一样。
评论 有用 1回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
墨值悬赏