完成SQL查询中的空白

你可以在下面试试-

select A.val,coalesce(cnt,0) from
(
select 1 as val 
union 
select 2 
union 
select 3 union selecct 4 union select 5 union select 6
)A left join 
(select number_of_awards, count(playerid) as cnt from table group by number_of_awards)B
on A.val=B.number_of_awards

一个常见的技巧是使用包含“合理”数字范围的数字表。

create table Numbers (
    N int primary key clustered not null
);
insert into Numbers Values (1, 2, 3, ..., <A reasonable value>);

你可以在这张桌子上加入。

select
    num.N
    , award_cnt.cnt
from
    Numbers as num
left join
    (
    select number_of_awards, count(playerid) as cnt from table group by number_of_awards
    ) as award_cnt
    on
        num.N = award_cnt.number_of_awards
order by
    num.N

您希望将其用于聚合,因此原始表中有您需要的行。所以,我想:

with naw as (
      select number_of_awards, count(playerid) as cnt
      from t
      group by number_of_awards
     ),
     n as (
      select n.n
      from (select row_number() over (order by playerid) as n
            from t
           ) n join
           (select max(number_of_awards) as maxn
            from naw
           ) maxn
           on n.n <= maxn
     )
select n.n, coalesce(cnt, 0) as cnt
from n left join
     naw
     on n.n = naw.number_of_awards
order by n.n;

根据数据库的不同,可能还有更简单的方法。例如,Postgres支持 generate_series() 这对这类问题很方便。