create table roy(A int,B int,C int,D int)
insert into roy select 1, 2, 0,-1
insert into roy select -2, 1,-1, 2
insert into roy select 1,-2, 2, 1
insert into roy select 0, 2, 0, 0
insert into roy select 2,-1,-2,-2
insert into roy select -1, 0, 1,-1
楼主结贴了,那我也回复一个动态的写法
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+cast(name as varchar)+']=sum(case '+name +' when b.id then 1 else 0 end)'
from syscolumns where id=object_id('roy')
set @sql= 'select b.id '+@sql+
' from roy,(select 2 as id union select 1 union select 0 union select -1 union select -2) b group by b.id'
exec(@sql)
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 1 1 1 2
0 1 1 2 1
1 2 1 1 1
2 1 2 1 1
declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2
declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2
select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from @t ,
(select * from @tb) b
group by b.id
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 2 2 2 2
0 1 1 1 1
1 1 1 1 1
2 1 1 1 1
declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2
declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2
select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from @t ,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by b.id
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 2 2 2 2
0 1 1 1 1
1 1 1 1 1
2 1 1 1 1
declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2
declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2
select
b.id,
sum(case A when 0 then 1 else 0 end) as A,
sum(case B when 1 then 1 else 0 end) as B,
sum(case C when 2 then 1 else 0 end) as C,
sum(case D when -1 then 1 else 0 end) as D
from @t ,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by b.id
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 2
-1 1 1 1 2
0 1 1 1 2
1 1 1 1 2
2 1 1 1 2
declare @t table(A int,B int,C int,D int)
insert into @t select 1, 2, 0,-1
insert into @t select -2, 1,-1, 2
insert into @t select 1,-2, 2, 1
insert into @t select 0, 2, 0, 0
insert into @t select 2,-1,-2,-2
insert into @t select -1, 0, 1,-1
select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from
@t a,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by
b.id
/*
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 1 1 1 2
0 1 1 2 1
1 2 1 1 1
2 1 2 1 1
*/
select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from
表 a,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by
b.id