有一表table1有很多字段,其中有如下两条 create table #t (name varchar(10), col int) insert into #t select 'a', 1 union all select 'b', 2 union all select 'c', 3 union all select 'a', 1 union all select 'a', 2 union all select 'b', 3 select distinct name,'ta=1'=(select count(*) from #t where a.name=name and col=1), 'ta=2'=(select count(*) from #t where a.name=name and col=2), 'ta=3'=(select count(*) from #t where a.name=name and col=3) from #t a--or select name , sum(case when col=1 then 1 else 0 end) as 'ta=1', sum(case when col=2 then 1 else 0 end) as 'ta=2', sum(case when col=3 then 1 else 0 end) as 'ta=3' from #t group by namename ta=1 ta=2 ta=3 ---------- ----------- ----------- ----------- a 2 1 0 b 0 1 1 c 0 0 1(3 row(s) affected)
create table #t
(name varchar(10), col int)
insert into #t
select 'a', 1 union all
select 'b', 2 union all
select 'c', 3 union all
select 'a', 1 union all
select 'a', 2 union all
select 'b', 3
select distinct name,'ta=1'=(select count(*) from #t where a.name=name and col=1),
'ta=2'=(select count(*) from #t where a.name=name and col=2),
'ta=3'=(select count(*) from #t where a.name=name and col=3)
from #t a--or
select name ,
sum(case when col=1 then 1 else 0 end) as 'ta=1',
sum(case when col=2 then 1 else 0 end) as 'ta=2',
sum(case when col=3 then 1 else 0 end) as 'ta=3'
from #t
group by namename ta=1 ta=2 ta=3
---------- ----------- ----------- -----------
a 2 1 0
b 0 1 1
c 0 0 1(3 row(s) affected)