MyBatis 语法不支持 having 节点

布朗克168

已于 2025-03-27 11:05:52 修改

阅读量255

点赞数 2

分类专栏： mybatis 文章标签： mybatis java 开发语言小知识点合集

于 2025-03-26 22:23:36 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_56693899/article/details/146542075

版权

mybatis 专栏收录该内容

2 篇文章

订阅专栏

MyBatis 不支持 having 节点

比如在 GROUP BY 之后添加了 HAVING 子句，其内容为SUM(vsbsad.business_income) >= 0，该子句会对分组后的 SUM(vsbsad.business_income) 结果进行过滤，仅保留求和结果不为负数的分组记录。但是试过不支持。可把 having 条件嵌入到 select 语句里，当作一个子查询，然后在外部查询里对该条件进行过滤

<select id="findStoreSaleInfoDetail" resultType="com.test.dto.res.store.StoreSaleBaseInfolDTO">
    SELECT *
    FROM (
        SELECT
            ds.id AS storeId,
            ds.store_name AS storeName,
            SUM(vsbsad.business_income) AS businessIncome
        FROM
            store ds
            INNER JOIN
            store_business_di vsbsad
            ON
                ds.id = vsbsad.store_id
        <where>
            <if test="startDate != null and endDate != null">
                and vsbsad.sdt between #{startDate} and #{endDate}
            </if>
            <!-- 新增 storeIds 过滤条件 -->
            <if test="req.storeIds != null and req.storeIds.size() > 0">
                AND ds.id IN
                <foreach item="storeId" collection="req.storeIds" open="(" separator="," close=")">
                    #{storeId}
                </foreach>
            </if>
        </where>
        GROUP BY
            ds.id,
    ) subquery
    WHERE subquery.businessIncome >= 0
</select>