性别查询优化

linwe

2023-12-18 059f59db209743b502110f7bcacc64afb1a822ad

性别查询优化

 src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml

@@ -267,14 +267,16 @@

    <select id="getHouseHoldStatistics" resultType="java.util.Map">
        SELECT
        CASE
        WHEN
        jh.gender = 1 THEN
        '男'
        WHEN jh.gender = 0 THEN
        '女' ELSE '未知'
        END AS gender,
        count( 1 ) numbers
        a.gender,
        count( a.gender ) numbers
        FROM
        ( SELECT
        IF
        (
        id_card IS NULL,
        '未知',
        IF
        (SUBSTRING( id_card, 17, 1 ) % 2 = 1, '男', '女' )) AS gender
        FROM
        jczz_household jh
        LEFT JOIN jczz_doorplate_address jda ON jh.house_code = jda.address_code
@@ -282,23 +284,22 @@
        jda.nei_code = #{code}
        AND jh.is_deleted = 0
        <if test="userId != null">
        AND jda.address_code IN (
        SELECT
        jgr.house_code
        FROM
        jczz_grid jg
        LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id
        LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
        WHERE
        jgm.user_id = #{userId}
        AND jh.house_code = jgr.house_code
        AND jg.is_deleted = 0
        )
            AND jda.address_code IN (
            SELECT
            jgr.house_code
            FROM
            jczz_grid jg
            LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id
            LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
            WHERE
            jgm.user_id = #{userId}
            AND jh.house_code = jgr.house_code
            AND jg.is_deleted = 0
            )
        </if>
        ) a
        GROUP BY
        gender


        a.gender
    </select>
    <select id="getHouseHoldStatisticsAge" resultType="java.util.Map">