一条SQL语句的写法

[gas_lb521]

表HMIS_ba_basy
字段有 id, a, b, c
1 是 是 是
2 否 否 否
3 是 否 是
4 否 是 是
用一条SQL语句统计出字段a=是,a=否,b=是,b=否,c=是,c=否的记录数

SELECT s1 = COUNT((CASE WHEN a= '是' THEN id ELSE 0 END))，s1 = COUNT((CASE WHEN a= '否' THEN id ELSE 0 END))，
FROM HMIS_ba_basy
这样统计把所有都统计出来啦

[jinjazz]

create table test(a varchar(10),b varchar(10),c varchar(10))

insert into test
select '是','是','是' union
select '否','否','否' union
select '是','否','是' union
select '否','是','是'

select
sum(case when a='是' then 1 else 0 end) as a是,
sum(case when a='否' then 1 else 0 end) as a否,
sum(case when b='是' then 1 else 0 end) as a是,
sum(case when b='否' then 1 else 0 end) as b否,
sum(case when c='是' then 1 else 0 end) as a是,
sum(case when c='否' then 1 else 0 end) as c否
from test
drop table test

/*

(4 行受影响)
a是 a否 a是 b否 a是 c否
----------- ----------- ----------- ----------- ----------- -----------
2 2 2 2 3 1

(1 行受影响)
*/

[jinjazz]

create table test(a varchar(10),b varchar(10),c varchar(10))

insert into test
select '是','是','是' union
select '否','否','否' union
select '是','否','是' union
select '否','是','是' 

select 
sum(case when a='是' then 1 else 0 end) as a是,
sum(case when a='否' then 1 else 0 end) as a否,
sum(case when b='是' then 1 else 0 end) as a是,
sum(case when b='否' then 1 else 0 end) as b否,
sum(case when c='是' then 1 else 0 end) as a是,
sum(case when c='否' then 1 else 0 end) as c否
 from test
drop table test

/*

(4 行受影响)
a是          a否          a是          b否          a是          c否
----------- ----------- ----------- ----------- ----------- -----------
2           2           2           2           3           1

(1 行受影响)
*/

[gas_lb521]

谢谢！