请教大佬怎么写个SQL语句获取12字段中的0与1个数

Question

select DIAOCJG1, DIAOCJG2, DIAOCJG3, DIAOCJG4, DIAOCJG5, DIAOCJG6, DIAOCJG7, DIAOCJG8, DIAOCJG9, DIAOCJG10, DIAOCJG11, DIAOCJG12, DIAOCJG13, DIAOCJG14, DIAOCJG15, DIAOCJG16, DIAOCJG17, DIAOCJG18, DIAOCJG19, DIAOCJG20
from table1
怎么操作这个20个字段获取1与0的个数

Answer 1

可以使用Case when,例如:

SELECT
(SUM(CASE WHEN def1=1 THEN 1 ELSE 0 END) +
SUM(CASE WHEN def2=1 THEN 1 ELSE 0 END) +
SUM(CASE WHEN def3=1 THEN 1 ELSE 0 END)) yi,
(SUM(CASE WHEN def1=0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN def2=0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN def3=0 THEN 1 ELSE 0 END)) ling
FROM stu;