--原始表
code
1
1
2
3
--查出来数据
code sum
1 2
2 1
3 1
4 0
5 0
------解决思路----------------------
create table test(code int)
go
insert into test values(1),(1),(2),(3)
go
with mt as
( select code , count(*) c from test group by code
)
select p.number , isnull(mt.c , 0) c
from master..spt_values p left join mt on p.number = mt.code
where p.type = 'p' and p.number between 1 and 5
go
drop table test
go
(4 行受影响)
number c
----------- -----------
1 2
2 1
3 1
4 0
5 0
(5 行受影响)
------解决思路----------------------
select 1 as code into #原始表
insert into #原始表 values(1)
insert into #原始表 values(2)
insert into #原始表 values(3)
select 1 as code into #临时表
insert into #临时表 values(2)
insert into #临时表 values(3)
insert into #临时表 values(4)
insert into #临时表 values(5)
select a.code,isnull(count(b.code),0) as sum
from #临时表 a left join #原始表 b
on a.code=b.code
group by a.code
/*
code sum
----------- -----------
1 2
2 1
3 1
4 0
5 0
(5 行受影响)
*/