gpt4 book ai didi

mysql - 是否有一个子查询可以使用同一表中不同列的最大日期来计算 datediff?

转载 作者:行者123 更新时间:2023-11-29 06:28:36 25 4
gpt4 key购买 nike

我正在尝试创建一个 MySQL 查询,该查询将查看一列,识别该列中的最新日期,然后在日期差计算中使用该日期来创建新列。

我已经尝试使用 WHERE 语句创建一个新的 maxdate,而不是在 datediff 计算中使用 Now(),但这只是给了我一个包含表中最旧记录的记录,这是差异最大的 datediff 。

SELECT 
table.firstname
table.first_order_day
table.last_order_day
DATEDIFF(MAX(last_order_day),first_order_day) AS "Customer Tenure"
DATEDIFF(MAX(last_order_day),last_order_day) AS "Days Since Last Order"
FROM table

预期/期望的结果(MaxDate取自last_order_day列:2019-09-01):

first name,first_order_day,last_order_day,Customer Tenure,Days Since Last Order

Andrew,2019-01-01, 2019-05-01, 243, 123
Audrey,2019-02-01, 2019-09-01, 212, 0
Matt,2019-03-01, 2019-06-01, 184, 92

Actual output:
Audrey,2019-02-01, 2019-09-01, 212, 0

最佳答案

您可以JOIN计算最大值的子查询:

SELECT 
t.firstname
t.first_order_day
t.last_order_day
DATEDIFF(x.last_order_day, t.first_order_day) AS "Customer Tenure"
DATEDIFF(x.last_order_day, t.last_order_day) AS "Days Since Last Order"
FROM
mytable t
CROSS JOIN (SELECT MAX(t.last_order_day) last_order_day FROM mytable) x

或者,如果您运行 MySQL 8.0,您也可以使用窗口函数:

SELECT 
firstname
first_order_day
last_order_day
DATEDIFF(MAX(last_order_day) OVER(), first_order_day) AS "Customer Tenure"
DATEDIFF(MAX(last_order_day) OVER(), last_order_day) AS "Days Since Last Order"
FROM
mytable

关于mysql - 是否有一个子查询可以使用同一表中不同列的最大日期来计算 datediff?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57997261/

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