如何让 GROUP BY 和 COUNT 包含零和? [英] How to have GROUP BY and COUNT include zero sums?
问题描述
我有这样的 SQL(其中 $ytoday
是 5 天前):
I have SQL like this (where $ytoday
is 5 days ago):
$sql = 'SELECT Count(*), created_at FROM People WHERE created_at >= "'. $ytoday .'" AND GROUP BY DATE(created_at)';
我希望它每天返回一个值,因此在这种情况下它会返回 5 个结果(5 天前到今天).
I want this to return a value for every day, so it would return 5 results in this case (5 days ago until today).
但是说 Count(*)
是昨天的 0,而不是返回零,它根本不返回该日期的任何数据.
But say Count(*)
is 0 for yesterday, instead of returning a zero it doesn't return any data at all for that date.
如何更改该 SQLite 查询,使其也返回计数为 0 的数据?
How can I change that SQLite query so it also returns data that has a count of 0?
推荐答案
如果没有复杂的(在我看来)查询,您的输出数据集将不会包含输入数据集中不存在的日期.这意味着您需要一个有 5 天时间加入的数据集.
Without convoluted (in my opinion) queries, your output data-set won't include dates that don't exist in your input data-set. This means that you need a data-set with the 5 days to join on to.
简单的版本是创建一个包含 5 个日期的表格,然后加入该表格.我通常会创建并保留(有效缓存) 一个包含我可能需要的每个日期的日历表.(例如从 1900-01-01 到 2099-12-31.)
The simple version would be to create a table with the 5 dates, and join on that. I typically create and keep (effectively caching) a calendar table with every date I could ever need. (Such as from 1900-01-01 to 2099-12-31.)
SELECT
calendar.calendar_date,
Count(People.created_at)
FROM
Calendar
LEFT JOIN
People
ON Calendar.calendar_date = People.created_at
WHERE
Calendar.calendar_date >= '2012-05-01'
GROUP BY
Calendar.calendar_date
这篇关于如何让 GROUP BY 和 COUNT 包含零和?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!