POSTGRES 多条件数量统计---CASE WHEN 妙用_pgsql case when 多个条件-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43317111/article/details/131117958

文章展示了如何使用SQL创建表格，插入预置数据，并执行统计查询来计算特定条件的记录数。此外，还介绍了在MyBatis中如何通过Mapper接口和XML配置进行类似的查询操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建表

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>