复杂查询封装

预计阅读时间： 约 6 分钟

在实际业务中，复杂查询是不可避免的的需求：多表关联查询、分组聚合统计、嵌套子查询等场景广泛存在。如何将这些复杂查询封装为简洁的 MyBatis 映射，同时保证查询性能，是进阶开发者必须掌握的技能。

一、多表 JOIN 查询封装

1.1 基础 JOIN 映射

<!-- 用户-部门-角色 三表关联 -->
<select id="getUserWithDeptAndRole" resultMap="userDetailMap">
    SELECT 
        u.id AS user_id,
        u.name AS user_name,
        u.email AS user_email,
        d.id AS dept_id,
        d.name AS dept_name,
        r.id AS role_id,
        r.name AS role_name
    FROM users u
    LEFT JOIN departments d ON u.dept_id = d.id
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
    WHERE u.id = #{userId}
</select>

<resultMap id="userDetailMap" type="UserDetail">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <result property="email" column="user_email"/>
    <association property="department" javaType="Department">
        <id property="id" column="dept_id"/>
        <result property="name" column="dept_name"/>
    </association>
    <association property="role" javaType="Role">
        <id property="id" column="role_id"/>
        <result property="name" column="role_name"/>
    </association>
</resultMap>

1.2 多对一关联（<association>）

适用于一对一或多数一场景：

<!-- 订单关联用户 -->
<resultMap id="orderUserMap" type="Order">
    <id property="id" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="amount" column="order_amount"/>
    <association property="user" javaType="User" columnPrefix="u_">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </association>
</resultMap>

<select id="getOrderWithUser" resultMap="orderUserMap">
    SELECT 
        o.id AS order_id,
        o.order_no,
        o.amount,
        u.id AS u_id,
        u.name AS u_name
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE o.id = #{orderId}
</select>

columnPrefix 技巧： 当多个关联表的列名相同时，使用 columnPrefix 区分来源。

1.3 一对多集合（<collection>）

<!-- 部门及其下属用户列表 -->
<resultMap id="deptWithUsersMap" type="Department">
    <id property="id" column="dept_id"/>
    <result property="name" column="dept_name"/>
    <collection property="users" ofType="User" columnPrefix="u_">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="email" column="email"/>
    </collection>
</resultMap>

<select id="getDeptWithUsers" resultMap="deptWithUsersMap">
    SELECT 
        d.id AS dept_id,
        d.name AS dept_name,
        u.id AS u_id,
        u.name AS u_name,
        u.email AS u_email
    FROM departments d
    LEFT JOIN users u ON d.id = u.dept_id
    WHERE d.id = #{deptId}
</select>

二、分组统计查询

2.1 GROUP BY 聚合封装

<!-- 各部门统计：人数、平均薪资、最高薪资 -->
<select id="getDeptStats" resultType="DeptStats">
    SELECT 
        d.id AS deptId,
        d.name AS deptName,
        COUNT(u.id) AS userCount,
        AVG(u.salary) AS avgSalary,
        MAX(u.salary) AS maxSalary,
        MIN(u.salary) AS minSalary
    FROM departments d
    LEFT JOIN users u ON d.id = u.dept_id
    GROUP BY d.id, d.name
    ORDER BY avgSalary DESC
</select>

2.2 多维分组统计

<!-- 按部门+职级分组统计 -->
<select id="getMultiDimensionStats" resultType="StatsResult">
    SELECT 
        d.name AS deptName,
        u.level AS userLevel,
        COUNT(*) AS userCount,
        SUM(u.salary) AS totalSalary,
        AVG(u.salary) AS avgSalary
    FROM users u
    JOIN departments d ON u.dept_id = d.id
    GROUP BY d.name, u.level
    WITH ROLLUP
</select>

2.3 条件聚合（HAVING 过滤）

<!-- 查询平均薪资超过阈值的部门 -->
<select id="getHighSalaryDepts" resultType="DeptStats">
    SELECT 
        d.id AS deptId,
        d.name AS deptName,
        COUNT(u.id) AS userCount,
        AVG(u.salary) AS avgSalary
    FROM departments d
    JOIN users u ON d.id = u.dept_id
    GROUP BY d.id, d.name
    HAVING AVG(u.salary) > #{threshold}
</select>

三、子查询优化

3.1 关联子查询 vs JOIN

子查询方式（不推荐，N+1 问题）：

<!-- 每条记录触发一次子查询 -->
<select id="getUsersWithDeptCount" resultType="UserWithCount">
    SELECT 
        u.id,
        u.name,
        (SELECT COUNT(*) FROM users u2 WHERE u2.dept_id = u.dept_id) AS deptUserCount
    FROM users u
</select>

JOIN 方式（推荐，单次查询）：

<!-- 先统计再 JOIN，避免 N+1 -->
<select id="getUsersWithDeptCount" resultType="UserWithCount">
    SELECT 
        u.id,
        u.name,
        dc.user_count AS deptUserCount
    FROM users u
    LEFT JOIN (
        SELECT dept_id, COUNT(*) AS user_count
        FROM users
        GROUP BY dept_id
    ) dc ON u.dept_id = dc.dept_id
</select>

3.2 IN 子查询优化

<!-- 使用 EXISTS 替代 IN（大数据集更优） -->
<select id="getUsersWithOrders" resultType="User">
    SELECT * FROM users u
    WHERE EXISTS (
        SELECT 1 FROM orders o 
        WHERE o.user_id = u.id 
        AND o.status = 'PAID'
    )
</select>

3.3 窗口函数优化分组查询

<!-- 使用窗口函数获取每组前 N 条 -->
<select id="getTopUsersPerDept" resultType="User">
    SELECT * FROM (
        SELECT 
            u.*,
            d.name AS dept_name,
            ROW_NUMBER() OVER (PARTITION BY u.dept_id ORDER BY u.salary DESC) AS rn
        FROM users u
        JOIN departments d ON u.dept_id = d.id
    ) ranked
    WHERE rn <= #{topN}
</select>

四、要点总结