按月分组，如果找不到记录，则返回0 [英] Group by Month, return 0 if no record found

问题描述

我想从数据库表中获取最近12个月的记录。

I want to fetch records from database table for last 12 months. Here is what I tried so far.

SELECT COUNT(s.id), date_part('month', s.viewed_at) month_number
    FROM statistics_maps_view as s
    INNER JOIN maps as m
    ON s.maps_id=m.id Where m.users_id = $users_id group by month_number ORDER BY month_number DESC LIMIT 12

我知道它将按月分组记录。但是如果没有特定月份的记录，有没有办法添加 Count = 0 ？

I know It'll group the records month wise. but is there a way to add Count = 0 if there is no record for a particular month?

推荐答案

如您所见， group by 子句不会在没有数据的地方创建条目。您可以做的是在整个结果左联接中包含另一个具有您想要的所有条目的结果集-例如，使用 generate_series ：

The group by clause will not create entries where there's no data, as you've seen. What you could do is left join this entire result with another result set that has all the entries you want - e.g., one you dynamically generate with generate_series:

SELECT    generate_series AS month_number, cnt
FROM      GENERATE_SERIES(1,12) g
LEFT JOIN (SELECT     COUNT(s.id) AS cnt, 
                      DATE_PART('month', s.viewed_at) AS month_number
           FROM       statistics_maps_view s
           INNER JOIN maps m ON s.maps_id = m.id 
           WHERE      m.users_id = $users_id 
           GROUP BY   month_number) s ON g.generate_series = s.month_number
ORDER BY  1 ASC

这篇关于按月分组，如果找不到记录，则返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！