mysql按日聚合数据_mysql – 在日期范围内按日期聚合数据,结果集中没有日期间隔...

65 阅读 0 评论 43 点赞

我是靠谱客的博主奋斗冬天，最近开发中收集的这篇文章主要介绍mysql按日聚合数据_mysql – 在日期范围内按日期聚合数据,结果集中没有日期间隔...，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

动态创建一系列日期并加入针对您的订单表： –

SELECT sub1.sdate, COUNT(ORDERS.id) as Norders

FROM

(

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY), "%M %e") as sdate

FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units

CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens

CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds

WHERE DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()

) sub1

LEFT OUTER JOIN ORDERS

ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%M %e")

GROUP BY sub1.sdate

这可以应对最多1000天的日期范围.

请注意,根据您在日期中使用的字段类型,可以更轻松地提高效率.

编辑 – 根据要求,获取每月订单数量： –

SELECT aMonth, COUNT(ORDERS.id) as Norders

FROM

(

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%Y%m") as sdate, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%M") as aMonth

FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)months

WHERE DATE_SUB(NOW(), INTERVAL months.i MONTH) BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW()

) sub1

LEFT OUTER JOIN ORDERS

ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%Y%m")

GROUP BY aMonth