gpt4 book ai didi

mysql - 使用MYSQL计算同一列中多个日期之间的时间

转载 作者:行者123 更新时间:2023-11-29 09:35:23 26 4
gpt4 key购买 nike

我有一个数据集,其中包含客户的编号和订单日期,我需要做的是计算每个客户的每个订单之间的天数,例如: enter image description here

所以我需要知道每个客户的每个订单之间的天数。感谢您的帮助:)

最佳答案

这是您将如何做到这一点,您可以使用dense_rank()

MySQL 8.0

select t1.id, t1.order_date
, ifnull(
datediff(cast(t1.order_date as datetime), cast(t2.order_date as datetime))
,0) as intrval
from (
select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
, order_date, id
from table1) as t1
left join
(
select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
, order_date, id
from table1) as t2
on t1.id = t2.id and t2.rnk+1 = t1.rnk

输出:

enter image description here

关于mysql - 使用MYSQL计算同一列中多个日期之间的时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57751914/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com