select a.type,count(*) from
(select 1 type union select 2 type union select 3) a left join [table] on a.type=[table].type group by a.type
(select 1 type union select 2 type union select 3) a left join [table] on a.type=[table].type group by a.type
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c2,
select a.type,a.count(*) from table right join Numtab b on a.type =b.N
where B.N in ('1','2','3') group by a.type,b.N
insert into #tmp(type) values('1')
insert into #tmp(type) values('2')
insert into #tmp(type) values('3')select a.type,isnull(count(b.*),0) from #tmp a left join table b
on a.type=b.type
报告说找不到a.type,为什么?
有什么简单的办法吗
insert #t select '1'
insert #t select '1'
insert #t select '1'
insert #t select '2'
insert #t select '2'select A.type,sum(case when #t.type is null then 0 else 1 end) as [count(*)] from #t right join (select '1' type union select '2' union select '3') as A
on A.type = #t.type
where A.type in ('1','2','3') group by A.type type count(*)
---- -----------
1 4
2 2
3 0(所影响的行数为 3 行)
select b.n,sum( case when a.type is not null then 1 else 0 end) from Numtab b left join #tmp a on a.type =b.N
where B.N in ('1','2','3')
group by b.n
from table
group by tpye
light jion 右相关错了不管
这样就没问题了