gpt4 book ai didi

MySQL 查询获取按月分组的过去 12 个月的数据,包括零计数

转载 作者:行者123 更新时间:2023-11-29 10:01:18 25 4
gpt4 key购买 nike

我有表订单(order_id、order_processed_date)。我想要过去 12 个月每月的订单数。如果任何月份有 0 个订单,则应打印 0。

类似这样的事情:

Total Orders | Month
-----------------------
2 | OCT 2018
3 | SEP 2018
0 | AUG 2018
0 | JUL 2018
1 | JUN 2018
0 | MAY 2018
0 | APR 2018
0 | MAR 2018
5 | FEB 2018
5 | JAN 2018
0 | DEC 2017
0 | NOV 2017

我已经在这个网站上搜索并得到了一些查询,但没有找到确切的解决方案。我使用下面的查询得到结果,但它没有年份:

SELECT 
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month,
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >=
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub

请有人帮我按我的要求提供结果。

最佳答案

以下查询构建前 12 个月每月第一天的列表。例如,2018 年 10 月 19 日 列表将包含从 2018 年 10 月 1 日2017 年 11 月 1 日 的日期。左连接完成剩下的工作:

SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date

关于MySQL 查询获取按月分组的过去 12 个月的数据,包括零计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52891799/

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