求助一个SQL语句

[myxchy]

有这样一个表
t1(A,B,C,D,E,F,G,K,L,M,N)全部为整形，并且只能取1，2，3，4这几个值例如
A B C D E F G K L M N
1 1 1 1 1 1 1 1 1 1 1
2 1 1 1 2 3 4 4 4 4 2
1 1 1 1 4 4 4 4 2 2 2
3 3 3 3 4 4 4 4 3 3 3
现在我想用最简单的语句统计A~N对应的1，2，3，4的个数，例如上例A中1的个数为2，2的个数为1，3的个数为1，4的个数为0

[mjhnet]

select
sum(case when A=1 then 1 else 0 end) as A1 ,
sum(case when A=2 then 1 else 0 end) as A2 ,
sum(case when A=3 then 1 else 0 end) as A3 ,
sum(case when A=4 then 1 else 0 end) as A4
sum(case when B=1 then 1 else 0 end) as B1 ,
sum(case when B=2 then 1 else 0 end) as B2 ,
sum(case when B=3 then 1 else 0 end) as B3 ,
sum(case when B=4 then 1 else 0 end) as B4
from t1

其余的类似写

[Rewiah]

select
sum(case when a=1 then 1 else 0 end) as a1,
sum(case when a=2 then 1 else 0 end) as a2,
...
sum(case when b=1 then 1 else 0 end) as b1,
...
sum(case when N=4 then 1 else 0 end) as N4
from t1

[mjhnet]

呵呵，少了个逗号

[bingofuel]

select count(*) from t1 group by A,order by A
然后用BCDE....替换A,要不写一个循环替换字段，但是感觉还不如这样来得快，因为字段不是很多

[ldcr520]

select
sum(case when a=1 then 1 else 0 end) as a1,
sum(case when a=2 then 1 else 0 end) as a2,
sum(case when a=3 then 1 else 0 end) as a3,
sum(case when a=4 then 1 else 0 end) as a4,
sum(case when b=1 then 1 else 0 end) as b1,
sum(case when b=2 then 1 else 0 end) as b2,
......
sum(case when n=4 then 1 else 0 end) as n4
from t1

[myxchy]

你们的写法我也知道，我是想有没有更少的语句？不过还是要感谢你们

[erigido]

建个存储过程，再循环调用吧

[happydreamer]

select count(*) from
(select a as num from table
union all select b from table
union all select c from table
...
) b
where b.num=1

[happydreamer]

就是把a,b,c,d..列并成一列 然后求和

[CrazyFor]

select 'a' ColName,a,count(*) [count] from t1 group by a
union all
select 'b',b,count(*) [count] from t1 group by b
union all
select 'c',c,count(*) [count] from t1 group by c
......

[fingerfox]

学习

[yijiayi]

select 'a' ColName,a,count(*) [count] from t1 group by a
union all
select 'b',b,count(*) [count] from t1 group by b
union all
select 'c',c,count(*) [count] from t1 group by c
......

select
sum(case when a=1 then 1 else 0 end) as a1,
sum(case when a=2 then 1 else 0 end) as a2,
sum(case when a=3 then 1 else 0 end) as a3,
sum(case when a=4 then 1 else 0 end) as a4,
sum(case when b=1 then 1 else 0 end) as b1,
sum(case when b=2 then 1 else 0 end) as b2,
......
sum(case when n=4 then 1 else 0 end) as n4
from t1