按日期范围内的日期汇总数据，结果集中没有日期间隔 [英] Aggregating data by date in a date range without date gaps in result set

问题描述

我有一张带有卖盘的表格，我想列出两个日期之间每天的卖盘COUNT，而不会留下日期间隔.

I have a table with sell orders and I want to list the COUNT of sell orders per day, between two dates, without leaving date gaps.

这是我目前拥有的:

SELECT COUNT(*) as Norders, DATE_FORMAT(date, "%M %e") as sdate 
FROM ORDERS 
WHERE date <= NOW() 
  AND date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

我得到的结果如下:

6     May 1
14    May 4
1     May 5
8     Jun 2
5     Jun 15

但是我想要得到的是:

6     May 1
0     May 2
0     May 3
14    May 4
1     May 5
0     May 6
0     May 7
0     May 8
.....
0     Jun 1
8     Jun 2
.....
5     Jun 15

有可能吗?

推荐答案

即时创建日期范围并将其与您的订单表结合起来:-

Creating a range of dates on the fly and joining that against you orders table:-

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天.

This copes with date ranges of up to 1000 days.

请注意，根据您用于日期的字段类型，可以轻松提高效率.

Note that it could be made more efficient easily depending on the type of field you are using for your dates.

编辑-根据要求，获取每月的订单数:-

EDIT - as requested, to get the count of orders per month:-

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

这篇关于按日期范围内的日期汇总数据，结果集中没有日期间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！