mysql - SQL 按范围分组并保留 0

Question

我刚刚阅读了此页面:In SQL, how can you "group by" in ranges?

回答对我有帮助，但我有一个问题，如果范围的 count() 等于 0，范围将不会显示，当 count() 等于 0 时如何显示范围？

这是我的查询:

select 
    w.Période, 
    nb from( select t.Période, count(*) as nb 
from (select 
          *, 
          case 
              when report.creation_date between '2017-05-28 00:00:00' and '2017-06-25 00:00:00' 
                  then 'P3' 
              when report.creation_date between '2017-06-25 00:00:00' and '2017-07-23 00:00:00' 
                  then 'P4' 
              when report.creation_date between '2017-07-23 00:00:00' and '2017-08-20 00:00:00' 
                  then 'P5' 
              when report.creation_date between '2017-08-20 00:00:00' and '2017-09-17 00:00:00' 
                  then 'P6' 
              else 'Avant' 
          end as Période 
      from report 
      where report.office_id = 11) as t 
      group by t.Période ) as w

这是我的结果:

Avant 57
P3 1
P5 2
P6 4

我想要:

Avant 57
P3 1
P4 0
P5 2
P6 4

我试过使用这些但它不起作用。 case when count() is null then 0 else count() end as nb 或者 case when count() = 0 then 0 else count() end as nb or case when count() REGEXP '[^[:digit:]]'然后 0 else count() 结束为 nb

Answer 1

您应该根据派生表加入它们:

SELECT t.period,
       COALESCE(s.nb,0) as yourCount
FROM(SELECT 'P1' as period 
     UNION ALL 
     SELECT 'P2' 
     UNION ALL 
     ...) t -- This is the derived table
LEFT JOIN(Your Query Here) s -- This is your query
 ON(t.period = s.period)