问题描述
- 求助 sql 语句 计数问题
-
表
name s1 s2 s3
zhang A A A
zhang A B B
zhang B B B
li A B B
li B B B结果
name s1A s1B s2A s2B s3A s3B
zhang 2 1 1 2 1 2
li 1 1 0 2 0 2问题：分别对每列的不同值进行计数，求助sql语句
解决方案
解决方案二：
select name,sum(
case
when s1='A' then 1
else 0
end) s1A,sum(
case
when s1='B' then 1
else 0
end) s1B,sum(
case
when s2='A' then 1
else 0
end) s2A,sum(
case
when s2='B' then 1
else 0
end) s2B,sum(
case
when s3='A' then 1
else 0
end) s3A,sum(
case
when s3='B' then 1
else 0
end) s3B
from csdn1
group by name;
解决方案三：
create table test_20151029(
name varchar2(50),
s1 varchar2(50),
s2 varchar2(50),
s3 varchar2(50)
);
SELECT t1.name
,SUM(decode(t1.m1
,1
,1)) s1a
,SUM(decode(t1.m1
,-1
,1)) s1b
,SUM(decode(t1.m2
,1
,1,0)) s2a
,SUM(decode(t1.m2
,-1
,1)) s2b
,SUM(decode(t1.m3
,1
,1,0)) s3a
,SUM(decode(t1.m3
,-1
,1)) s3b
FROM (SELECT t.name
,t.s1
,CASE
WHEN t.s1 = 'A' THEN
1
ELSE
-1
END m1
,t.s2
,CASE
WHEN t.s2 = 'A' THEN
1
ELSE
-1
END m2
,t.s3
,CASE
WHEN t.s3 = 'A' THEN
1
ELSE
-1
END m3
FROM test_20151029 t) t1
GROUP BY t1.name