如果group by不返回任何值，则显示零 [英] show a zero if group by returns no value

问题描述

我正在尝试获取过去7天注册的用户数量以显示在图表上，如果mysql返回0而不是没有行，那将是很好的.目前，它每天只返回一行具有值的行

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);

推荐答案

尝试:

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)

这篇关于如果group by不返回任何值，则显示零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！