MYSQL多表关联分组查询统计数量

<resultMap id="baseAmount" type="***.***VO">
    <result column="id" property="id"/>
    <result column="personType" property="personType"/>
    <result column="plaitAmount" property="plaitAmount"/>
    <result column="strengthAmount" property="strengthAmount"/>
</resultMap>

<select id="getPlaitStrength" resultMap="baseAmount">
    SELECT
        pe.person_type as personType,
        COUNT(1) as strengthAmount,
        g.id as id,
        g.plaitAmount as plaitAmount
    FROM t_person as pe
        LEFT JOIN (
            SELECT
                bz.id as id,
                bz.person_type AS personType,
                bz.plait_amount AS plaitAmount,
                bz.department_id AS departmentId
            FROM t_preparation_rybzsbz as bz
                LEFT JOIN t_department as d
                ON bz.department_id = d.id
            and bz.deleted = ${@com.aisino.guard.vo.system.TUserVO@DELTED_FLAG_N}
            where bz.department_id = #{parameter.departmentId}
        ) g
        ON g.departmentId = pe.department_id
            AND g.personType = pe.person_type
            and pe.deleted = ${@com.aisino.guard.vo.system.TUserVO@DELTED_FLAG_N}
        where pe.department_id = #{parameter.departmentId}
    GROUP BY pe.id
</select>

转载于:https://www.cnblogs.com/it-way/p/11188474.html