不同where条件合并输出

努力的家伙是不讨厌的

已于 2022-07-25 16:16:40 修改

阅读量319

点赞数 1

分类专栏：数据库文章标签：数据库 sql mysql

于 2022-07-21 23:18:32 首次发布

本文链接：https://blog.csdn.net/weixin_45386898/article/details/125922996

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

 <select id="getVillageList" resultType="com.netintech.app.domain.Community">
        SELECT

(SELECT count(1) FROM biz_person
 WHERE type &lt; 3 and villageCode=#{code}) as numberOfTenants,(SELECT count(1) FROM biz_person
 WHERE type = 3 and villageCode=#{code})as residents,(SELECT count(1) FROM biz_person
 WHERE type = 4 and villageCode=#{code})as numberOfForeigners,(SELECT count(DISTINCT buildingCode)
    FROM biz_person
    WHERE
    villageCode = #{code}) as buildingCount,
    	(SELECT villageName
    FROM biz_person
    WHERE
    villageCode=#{code}
		LIMIT 1

    )  as name,


    #{code} as code

    </select>

SELECT
        villageName as name,
        villageCode as code,
        sum(case when type &lt; 3 then 1 else 0 end) numberOfTenants,
        sum(case when type = 3 then 1 else 0 end) residents,
        sum(case when type = 4 then 1 else 0 end) numberOfForeigners,
        count(DISTINCT buildingCode) as buildingCount
        FROM
        biz_person
        WHERE
        townCode = #{param1}
        AND communityCode = #{param2}
        AND villageCode in
        <foreach collection="codes" item="code" open="(" separator="," close=")">
            #{code}
        </foreach>
        group by villageCode