sql - 显示非结果行 SQL (groupby - count)

Question

我在查询 (MS SQL Server) 时遇到问题，我想知道是否可以执行以下操作以及如何正确执行。

这是查询:

select numTenants, count(codSite) numSites
from (select case count(st1.name) when 0 then '0T'
                       when 1 then '1T' 
                       when 2 then '2T' 
                       when 3 then '3T'
                       when 4 then '4T'
                       else 'More than 4T' end numTenants, os1.siteCode as codSite
    from fl_OperativeSite os1 left join fl_SiteTenant st1
        on st1.fkOperativeSite=os1.pkOperativeSite
    where  os1.siteType='A' and os1.externalInfrastructure=2
    group by os1.siteCode) groups
group by numTenants
order by numTenants

这是结果:

numTenants     numSites
   1T             2957
   2T              553
   3T             1503
   4T              423
More than 4T       131

因为显然没有 numTenants 为 0 的“站点”。

我想问的是:有没有办法让结果显示如下？

numTenants     numSites
   0T                0
   1T             2957
   2T              553
   3T             1503
   4T              423
More than 4T       131

非常感谢!

Answer 1

这很简单:选择所有“数字”并外部连接您的查询:

select numbers.str, results.numSites
from 
(
    select '0T' as str union all
    select '1T' union all
    select '2T' union all
    select '3T' union all
    select '4T' union all
    select 'More than 4T'
) numbers
left join
(
  select numTenants, count(codSite) numSites
  from 
  (
    select 
      case count(st1.name) 
        when 0 then '0T'
        when 1 then '1T' 
        when 2 then '2T' 
        when 3 then '3T'
        when 4 then '4T'
        else 'More than 4T' 
      end numTenants,
      os1.siteCode as codSite
    from fl_OperativeSite os1 
    left join fl_SiteTenant st1
           on st1.fkOperativeSite=os1.pkOperativeSite
    where os1.siteType='A' and os1.externalInfrastructure=2
    group by os1.siteCode) groups
  group by numTenants
) results on results.numTenants = numbers.str
order by numbers.str;