--> Test Data: @T declare @T table ([ID] int,[lie1] int,[lie2] int,[lie3] int) insert into @T select 1,2,1,1 union all select 2,0,1,1 union all select 3,0,1,1--select * from @T --Code select [lie1] as lie,'lie1' as flag into # from @T union all select [lie2] ,'lie2' from @T union all select [lie3] ,'lie3' from @Tselect 题目=flag, [0的个数]=sum(case [lie] when 0 then 1 else 0 end), [1的个数]=sum(case [lie] when 1 then 1 else 0 end), [2的个数]=sum(case [lie] when 2 then 1 else 0 end) from # group by flag drop table #--Drop--Result /* 题目 0的个数 1的个数 2的个数 ---- ----------- ----------- ----------- lie1 2 0 1 lie2 0 3 0 lie3 0 3 0 */
create table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int) insert into T select 2,1,0 union all select 0,1,1 union all select 0,2,1 union all select 1,1,2 union all select 0,0,1 union all select 1,2,2 declare @s1 nvarchar(4000); declare @s2 nvarchar(4000); declare @s3 nvarchar(4000); set @s1='标题=''lie1''' set @s2='''lie2''' set @s3='''lie3'''Select @s1=@s1+','+quotename(lie1)+'=sum(case when [lie1]='+quotename([lie1],'''')+' then 1 else 0 end)' from T group by [lie1] Select @s2=@s2+','+'sum(case when [lie2]='+quotename([lie2],'''')+' then 1 else 0 end)' from T group by [lie2] Select @s3=@s3+','+'sum(case when [lie3]='+quotename([lie3],'''')+' then 1 else 0 end)' from T group by [lie3]exec('select '+@s1+' from T union all select '+@s2+' from T union all select'+@s3+' from T') drop table T /*标题 0 1 2 ---- ----------- ----------- ----------- lie1 3 2 1 lie2 1 3 2 lie3 1 3 2*/
select count(*) as total,line1 group by line1 order by total union all select count(*) as total,line2 group by line2 order by total union all select count(*) as total,line3 group by line3 order by total
priovt 倒置行和列
楼主,我这个是应该是正确的: 应该分三步走,三个操作分别分类统计三个字段,分别按三个字段汇总(必须这样做) -- 选项1统计 select lie1, count(*) as 统计数量 from test1 group by lie1 -- 选项2统计 select lie2, count(*) as 统计数量 from test1 group by lie2 -- 选项3统计 select lie3, count(*) as 统计数量 from test1 group by lie3别忘了给分哦!!
group by ...
declare @T table ([ID] int,[lie1] int,[lie2] int,[lie3] int)
insert into @T
select 1,2,1,1 union all
select 2,0,1,1 union all
select 3,0,1,1--select * from @T
--Code
select [lie1] as lie,'lie1' as flag into # from @T
union all
select [lie2] ,'lie2' from @T
union all
select [lie3] ,'lie3' from @Tselect 题目=flag,
[0的个数]=sum(case [lie] when 0 then 1 else 0 end),
[1的个数]=sum(case [lie] when 1 then 1 else 0 end),
[2的个数]=sum(case [lie] when 2 then 1 else 0 end)
from #
group by flag drop table #--Drop--Result
/*
题目 0的个数 1的个数 2的个数
---- ----------- ----------- -----------
lie1 2 0 1
lie2 0 3 0
lie3 0 3 0
*/
create table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int)
insert into T select
2,1,0 union all select
0,1,1 union all select
0,2,1 union all select
1,1,2 union all select
0,0,1 union all select
1,2,2
declare @s1 nvarchar(4000);
declare @s2 nvarchar(4000);
declare @s3 nvarchar(4000);
set @s1='标题=''lie1'''
set @s2='''lie2'''
set @s3='''lie3'''Select @s1=@s1+','+quotename(lie1)+'=sum(case when [lie1]='+quotename([lie1],'''')+' then 1 else 0 end)' from T group by [lie1]
Select @s2=@s2+','+'sum(case when [lie2]='+quotename([lie2],'''')+' then 1 else 0 end)' from T group by [lie2]
Select @s3=@s3+','+'sum(case when [lie3]='+quotename([lie3],'''')+' then 1 else 0 end)' from T group by [lie3]exec('select '+@s1+' from T union all select '+@s2+' from T union all select'+@s3+' from T')
drop table T
/*标题 0 1 2
---- ----------- ----------- -----------
lie1 3 2 1
lie2 1 3 2
lie3 1 3 2*/
union all
select count(*) as total,line2 group by line2 order by total
union all
select count(*) as total,line3 group by line3 order by total
应该分三步走,三个操作分别分类统计三个字段,分别按三个字段汇总(必须这样做)
-- 选项1统计
select lie1, count(*) as 统计数量 from test1 group by lie1
-- 选项2统计
select lie2, count(*) as 统计数量 from test1 group by lie2
-- 选项3统计
select lie3, count(*) as 统计数量 from test1 group by lie3别忘了给分哦!!