在开发中遇到一个问题,需要查询并统计出多个字段在不同条件下的数据,以前可能只是单纯的返回某几个字段数据,在经过一番查找和自己的调试过后总结出来以下的办法,代码都是经过测试并运行的,应该没有问题,其中count就是对数据进行计数,也可以使用sum来对数据进行累加。case when 后面是判断条件,可以使用and来继续连接其他条件,最后使用end结尾 ,as 后面是返回map类型时的key。
select
count(case when t.ZDMC_1 in ('张三') and t.DS_0 in ('北京') then t.ZDMC_1 end ) as "wh_dq" ,
count(case when t.ZDMC_1 in ('张三') and t.DS_0 in ('北京') then t.ZDMC_1 end ) as "wh_dqhj",
count(case when t.ZDMC_1 in ('张三') and t.DS_0 in ('北京') then t.ZDMC_1 end ) as "wh_sd"
from DP t
where t.UPDATE_TIME like '%2019-05%';
select
sum(case when t.ITEM1_2 in ('张三') and t.DS_1 in ('北京') then t.GROUP_0 else '0' end ) as "wh_dq" ,
sum(case when t.ITEM1_2 in ('张三') and t.DS_1 in ('北京') then t.GROUP_0 else '0' end ) as "wh_dqhj",
sum(case when t.ITEM1_2 in ('张三') and t.DS_1 in ('北京') then t.GROUP_0 else '0' end ) as "wh_sd"
from DP t
where t.UPDATE_TIME like '%2019-05%'