POSTGRES 多条件数量统计---CASE WHEN 妙用

创建表

create table tbl_user(

    id serial PRIMARY KEY,

    name varchar(256),

    addr varchar(256),

    age int,

    score int,

    fav varchar(256)

)

插入预置数据

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10, 23,'aaa_fav_new'),

('bbb','ccc_addr',10, 23,'bbb_fav_new'),

('ddd','ddd_addr',10, 22,'ccc_fav_new'),

('eee','eee_addr',10, 22,'ddd_fav_new')

ON conflict(name,addr,age) DO

UPDATE set score=excluded.score, fav=excluded.fav;

执行统计查询语句

select

    sum(case when name = 'aaa' and addr ='aaa_addr' then 1 else 0 end)+

    sum(case when name = 'ddd' and addr ='ddd_addr' then 1 else 0 end)+

    sum(case when name = 'eee' and addr ='eee_addr' then 1 else 0 end)+

    sum(case when name = 'bbb' and addr ='bbb_addr' then 1 else 0 end) as num

from tbl_user;

mybatis的mapper文件：

Mapper接口定义: int queryNum(List<User> userList);

mapper文件定义：
<select id="queryNum" resultType="java.lang.Integer">
    select
        <foreach item="item" collection="list" separator="+">
            sum(case when name = #{item.name} and addr = #{item.addr} then 1 else 0 end)
         </foreach>
        as num
    from tbl_user;
</select>