--> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int,[name] varchar(6),[state] int) insert [a] select 1,'a',1 union all select 2,'c',2 union all select 3,'d',3 union all select 4,'b',4 union all select 5,'a',2 union all select 6,'b',1 union all select 7,'c',3 --select * from a select [name], sum(case when state=1 then 1 else 0 end) as [state1], sum(case when state=2 then 1 else 0 end) as [state2], sum(case when state=3 then 1 else 0 end) as [state3], sum(case when state=4 then 1 else 0 end) as [state4] from a group by [name] name state1 state2 state3 state4 ------ ----------- ----------- ----------- ----------- a 1 1 0 0 b 1 0 0 1 c 0 1 1 0 d 0 0 1 0(4 行受影响)
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[name] varchar(6),[state] int)
insert [a]
select 1,'a',1 union all
select 2,'c',2 union all
select 3,'d',3 union all
select 4,'b',4 union all
select 5,'a',2 union all
select 6,'b',1 union all
select 7,'c',3 --select * from a select [name],
sum(case when state=1 then 1 else 0 end) as [state1],
sum(case when state=2 then 1 else 0 end) as [state2],
sum(case when state=3 then 1 else 0 end) as [state3],
sum(case when state=4 then 1 else 0 end) as [state4]
from a
group by [name]
name state1 state2 state3 state4
------ ----------- ----------- ----------- -----------
a 1 1 0 0
b 1 0 0 1
c 0 1 1 0
d 0 0 1 0(4 行受影响)