declare @t table(id int, name varchar) insert @t select 1 ,'a' union all select 2 ,'a' union all select 3 , 'c' union all select 1, 'a' select distinct a.id,isnull(cnt,0) from @t a left join (select id,count(*) cnt from @t where name='a' group by id) b on a.id=b.id
DECLARE @A TABLE(ID INT,NAME VARCHAR(1)) INSERT INTO @A SELECT 1,'A' INSERT INTO @A SELECT 2,'A' INSERT INTO @A SELECT 3,'C' INSERT INTO @A SELECT 1,'A' SELECT ID , COUNT(CASE WHEN NAME='A' THEN 1 END) AS COUNT FROM @A GROUP BY ID
select id,sum(case [name] when 'a' then 1 else 0 end ) as count from @test group by id
可以不用sum啊,select id,case name when 'a' then count(id) else 0 end [count] from @tt group by id,name
如果用count的话,可以改为select id,count(case when name='a' then 1 else null end) as [count] from test group by id
insert @t select 1 ,'a'
union all select 2 ,'a'
union all select 3 , 'c'
union all select 1, 'a' select distinct a.id,isnull(cnt,0) from @t a
left join
(select id,count(*) cnt from @t where name='a' group by id) b
on a.id=b.id
INSERT INTO @A SELECT 1,'A'
INSERT INTO @A SELECT 2,'A'
INSERT INTO @A SELECT 3,'C'
INSERT INTO @A SELECT 1,'A'
SELECT ID , COUNT(CASE WHEN NAME='A' THEN 1 END) AS COUNT FROM @A GROUP BY ID
from test
group by id