case when then else end 语句遇上sum或count等统计函数的注意事项（列转行）

这里需要注意count函数里面包围case when then else end的用法；还有一点需注意的是：count(null)得到的是0，这表明不符合当前条件下，走的是else null 这时候count(null)就为0啦~~，即不在当前条件下统计，否则count(1)就是统计了！！！

用count

select username,

count(case when formid=‘mFormFlow009’ then 1 else null end) as “亮点”,

count(case when formid=‘mFormFlow010’ then 2 else null end) as “提案”,

count(case when formid=‘mFormFlow011’ then 3 else null end) as “课题”

from eip_points group by username

用sum

select username,

count(case when formid=‘mFormFlow009’ then 1 else 0 end) as “亮点”,

count(case when formid=‘mFormFlow010’ then 2 else 0 end) as “提案”,

count(case when formid=‘mFormFlow011’ then 3 else 0 end) as “课题”

from eip_points group by username