在mysql查询中显示缺少日期的计数 [英] Display counts for missing dates in mysql query

问题描述

在运行以下查询后：

SELECT 
    DATE_FORMAT( added_datetime, '%Y-%m-%d' ) AS date, 
    SUM( gender = 'male' ) AS male, 
    SUM( gender = 'female' ) AS female
FROM social_user 
WHERE social_network = 'FBuser' 
AND date( added_datetime ) BETWEEN date('2014-11-18') AND date('2014-11-20')
GROUP BY date( added_datetime )

我得到这个结果：

date        | male | female
------------+------+--------
2014-11-19  |    2 |      0

但我需要一些不同于此截图的东西：

But I need something different like this screenshot:

date        | male | female
------------+------+--------
2014-11-18  |    0 |      0
2014-11-19  |    1 |      0
2014-11-20  |    2 |      0
2014-11-21  |    0 |      0
...    
...

我需要我的所有日​​期结果集是我在运营商之间使用的。

I need all date in my result set which is I used in my between operator.

对不起，我的英文不好，附加图片的声誉不足。

Sorry for my poor English and I don't have enough reputation for attached image.

推荐答案

数据中不存在的日期不会神奇地显示在结果中。一个解决方案是创建一个包含很长时间内所有日期的日期表：

The dates that do not exist in your data cannot magically appear in the results. One solution is to create a table of dates that contains all the dates inside a very long timespan:

CREATE TABLE datelist (DATE DATETIME NOT NULL PRIMARY KEY);
-- dates in the past
INSERT INTO datelist VALUES ('2014-11-18');
INSERT INTO datelist VALUES ('2014-11-19');
INSERT INTO datelist VALUES ('2014-11-20');
INSERT INTO datelist VALUES ('2014-11-21');
-- dates in the future

并在您的JOIN查询中使用它：

And use it in your JOIN query:

SELECT
    datelist.date,
    SUM(gender = 'male') AS male,
    SUM(gender = 'female') AS female
FROM datelist
LEFT JOIN social_user ON datelist.date = DATE(social_user.added_datetime)
WHERE datelist.date BETWEEN '2014-11-18' AND '2014-11-20' AND (
    social_user.id /* or whatever primary key */ IS NULL OR social_network = 'FBuser'
)
GROUP BY datelist.date

示例输出：

date                 male  female
-------------------  ----  ------
2014-11-18 00:00:00  NULL  NULL
2014-11-19 00:00:00  2     0
2014-11-20 00:00:00  1     2

在上面的例子中，两个NULL列表示没有匹配的社会的用户当天的记录。 WHERE 子句被调整为包含这些行。

In the above example, the two NULL columns indicates that there is no matching social_user record for that day. The WHERE clause is tweaked to include such rows.

这篇关于在mysql查询中显示缺少日期的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！