• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求助一个SQL语句

myxchy 2003-05-07 10:02:17
有这样一个表
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
...全文
12 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yijiayi 2003-09-05
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
回复
fingerfox 2003-05-07
学习
回复
CrazyFor 2003-05-07
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
......

回复
happydreamer 2003-05-07
就是把a,b,c,d..列并成一列 然后求和
回复
happydreamer 2003-05-07
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
回复
erigido 2003-05-07
建个存储过程,再循环调用吧
回复
myxchy 2003-05-07
你们的写法我也知道,我是想有没有更少的语句?不过还是要感谢你们
回复
ldcr520 2003-05-07
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
回复
bingofuel 2003-05-07
select count(*) from t1 group by A,order by A
然后用BCDE....替换A,要不写一个循环替换字段,但是感觉还不如这样来得快,因为字段不是很多
回复
mjhnet 2003-05-07
呵呵,少了个逗号
回复
Rewiah 2003-05-07
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 2003-05-07
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

其余的类似写
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-07 10:02
社区公告
暂无公告