如何在一周中找到差异并在另一列中显示

Question

SELECT COUNT(transaction.id) AS transactionCount, 
YEARWEEK(transaction.orderdate) AS orderDate, 
YEARWEEK(user.created_on) AS userJoinDate, 
"difference in weeks", 
user.fullname
FROM transaction
LEFT JOIN user ON transaction.id_user = user.id
WHERE transaction.completed IS NOT NULL  
GROUP BY user.id, 
orderDate
ORDER BY userJoinDate ASC, 
`user`.`fullname` ASC, 
orderDate ASC

我只想在几周内得到与显示的userJoinDate的差异。

Answer 1

1. 如果您需要周的差异，那么您可以使用TIMESTAMPDIFF((。请记住，它以完整的单位计算差异(即执行FLOOR，而不是ROUND(。因此，如果你需要在ROUNDing中，那么在第三个参数中添加半周(即添加7*24/2=84小时的间隔(
2. 所有未用作聚合函数参数的列(在输出列表、HAVING、ORDER BY、窗口函数中提到(都必须包含在GROUP BY表达式中。输出列可以通过它们在GROUP by和ORDER by中的位置来引用

SELECT COUNT(transaction.id) AS transactionCount, 
YEARWEEK(transaction.orderdate) AS orderDate, 
YEARWEEK(user.created_on) AS userJoinDate, 
TIMESTAMPDIFF(WEEK, user.created_on, transaction.orderdate) difference_in_weeks, 
user.fullname
FROM transaction
LEFT JOIN user ON transaction.id_user = user.id
WHERE transaction.completed IS NOT NULL  
GROUP BY 2, 3, 4, 5   -- output columns numbers
ORDER BY 3, 5, 2