mysql group by不满足返回0,如果group by不返回值，则显示零

I am trying to get the amount of users signed up in the past 7 days to display on a chart and it would be nice if mysql returned 0 instead of no row. Currently it just returns a row for each day THAT has a value

SELECT date(created_at),

count(id)

FROM user_accts

WHERE date(created_at) < NOW()

AND date(created_at) > DATE_SUB(NOW(), INTERVAL 7 DAY)

GROUP BY date(created_at);

解决方案

Try:

SELECT DATE_SUB(CURDATE(), INTERVAL i DAY) date_created_at,

count(id)

FROM (SELECT 1 i UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7) i

LEFT JOIN user_accts ON date(created_at) = DATE_SUB(CURDATE(), INTERVAL i DAY)

/*AND owner_id = '131'*/

GROUP BY DATE_SUB(CURDATE(), INTERVAL i DAY)