动态SQL

最新推荐文章于 2024-07-31 19:38:21 发布

囂蘳

最新推荐文章于 2024-07-31 19:38:21 发布

阅读量138

点赞数 3

文章标签： sql 数据库 mybatis

本文链接：https://blog.csdn.net/GJX00/article/details/138093045

版权

文章讲述了公司TMS培训系统的管理员如何通过MyBatisXML查询学员的证书，涉及多维度查询和JOIN操作。作者详细解释了SQL语句的选择过程，并指出在处理一对多关系时，为何使用GROUPBY和HAVINGCOUNT(DISTINCT)来确保查询结果的准确性。

摘要由CSDN通过智能技术生成

前言

最近公司的TMS（培训系统）有一个业务场景，管理员可以多维度的查看学员获得的证书，学员的证书是学员通过考试后获取的。

管理员可以选择证书组合还查看获取的人员

主要涉及到的数据库的两个表，一个是users(学员表) cert_user(证书学院关联表)
主要的含义就是他可以选择多个证书的组合，来查看获取到的人员。
mybatis的xml代码：

<select id="certUserPaginate" resultType="xyz.playedu.common.dto.UserDTO">
        select u.name, u.email, u.avatar from users u
        <choose>
            <when test="type == 'obtain'">
                inner join cert_user cu on u.id = cu.user_id
                <where>
                    <!-- 符合条件的查询条件 -->
                    <if test="userName != null and userName != ''">
                        AND u.name LIKE CONCAT('%', #{userName}, '%')
                    </if>
                    <if test="certIds != null and certIds.size() != 0">
                        AND cu.cert_id IN
                        <foreach collection="certIds" item="certId" open="(" separator="," close=")">
                           #{certId}
                        </foreach>
                    </if>
                </where>
                GROUP BY u.id HAVING COUNT(DISTINCT cu.cert_id) = #{certSize}
                LIMIT #{pageStart}, #{pageSize}
            </when>
            <when test="type == 'unObtain'">
                left join cert_user cu on u.id = cu.user_id
                <where>
                    <!-- 不符合条件的查询条件 -->
                    <if test="userName != null and userName != ''">
                        AND (u.name NOT LIKE CONCAT('%', #{userName}, '%'))
                    </if>
                    <if test="certIds != null and certIds.size() != 0">
                        AND (cu.cert_id IS NULL OR cu.cert_id NOT IN
                        <foreach collection="certIds" item="certId" open="(" separator="," close=")">
                            #{certId}
                        </foreach>)
                    </if>
                </where>
                LIMIT #{pageStart}, #{pageSize}
            </when>
        </choose>
    </select>

首先使用标签来表示不同的状态执行的sql语句
表示的是，当我们传过来的type的值为obtain表示当前要执行的SQL语句是查询获取了选择的证书组合的人员
拼接起来的语句就是
select u.name, u.email, u.avatar from users u inner join cert_user cu on u.id =
cu.user_id

<where>
                    <!-- 符合条件的查询条件 -->
                    <if test="userName != null and userName != ''">
                        AND u.name LIKE CONCAT('%', #{userName}, '%')
                    </if>
                    <if test="certIds != null and certIds.size() != 0">
                        AND cu.cert_id IN
                        <foreach collection="certIds" item="certId" open="(" separator="," close=")">
                           #{certId}
                        </foreach>
                    </if>
                </where>

上面这一部分的代码则表示，如果我们传了userName 就进行模糊查询

 GROUP BY u.id HAVING COUNT(DISTINCT cu.cert_id) = #{certSize}
                LIMIT #{pageStart}, #{pageSize}

解释一下为什么要使用group by
因为我们users表与cert_user表其实相当于一对多的状态，如果我们不分组而是直接用in去限制

//举一个例子
如果 我们要的证书的组合是  id为  1和17的和19。 那么在sql里面就是  cert_id in (1,179,19)
那么就会出现一个问题，那就是 如果一个学员只获得了  1和17  也会被查出来
这个时候我们就可以利用分组  按照学员id来分组，HAVING COUNT(DISTINCT cu.cert_id) = #{certSize}这段代码就是为了限制数据   比如   我指定了  证书组合是  1和17   那么分组后他们的数据就只有两条   certSize是我传入的参数 ，
这里代码其实有bug   如果用户获得了  1  17  19两个证书   但是你查询条件COUNT(DISTINCT cu.cert_id) = #{certSize}  所以应该对这进行修改   应该是 COUNT(DISTINCT cu.cert_id) <= #{certSize}他即可