sql计算个数

[happyxzxin]

数据库每一个单元格只有0，1，2的一个数
把他们添加都数据库里面是这样的
ID lie1 lie2 lie3
1 2 1 1
2 0 1 1
3 0 1 1
。。。。。。

我想把数据库里面每一列的0 ，1，2 统计出来，然后把它放到一个dataset，怎么做呢？
怎么样能方便的反馈出来呢，我做的是一个投票系统，0 1 2分别代表对某个选项（列）的满意程度，一个id表示一次投票

[nujiah001]

up

[llsen]

selct count(*) from ,,
group by ...

[wxg22526451]

--> 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 @T

select 题目=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
*/

[jiangshun]

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



*/

[Fibona]

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

[mengxj85]

Up，行列转换
[Quote=引用 3 楼 wxg22526451 的回复:]
SQL code--> 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 @T

select 题目=flag,
[0的个数]=sum(case [lie] whe…
[/Quote]

[happyxzxin]

能不能弄个ACCESS能用的？谢谢了

[happyxzxin]

请说明白些，看不懂啊！

[claro]

帮顶。

[vip_hjb]

priovt 倒置行和列

[jingkunli]

楼主,我这个是应该是正确的:
应该分三步走,三个操作分别分类统计三个字段,分别按三个字段汇总(必须这样做)
-- 选项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

别忘了给分哦!!