MySQL 解决子查询中排序后分组不生效问题

最新推荐文章于 2023-12-20 17:23:32 发布

Say Bay To The Bugs

最新推荐文章于 2023-12-20 17:23:32 发布

阅读量541

点赞数 3

分类专栏： MYSQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_42678469/article/details/128617709

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL 解决子查询中排序后分组不生效问题

我这里的需求是按照投递时间排序后，通过任务ID分组取最新投递的任务数据

两种解决办法：

子查询中添加DISTINCT关键字

SELECT *
        FROM (SELECT DISTINCT 
        			 mtd.id,
                     mt.id   AS miniTaskId,
                     mt.money,
                     mt.state,
                     mt.roshi_coin,
                     pd.name AS positionName,
                     pd.working_place,
                     pd.quantity_required
              FROM mini_task_detail mtd
                       LEFT JOIN mini_task mt ON mtd.mini_task_id = mt.id
                       LEFT JOIN project_demand pd on mt.project_demand_id = pd.id
              WHERE mtd.is_deleted = 0
                AND mtd.type = 6
                AND mtd.sys_user_id = #{userId}
                ORDER BY mtd.deliver_time DESC
              ) mtd
        GROUP BY mtd.miniTaskId

在排序前加 HAVING 1

SELECT *
        FROM (SELECT mtd.id,
                     mt.id   AS miniTaskId,
                     mt.money,
                     mt.state,
                     mt.roshi_coin,
                     pd.name AS positionName,
                     pd.working_place,
                     pd.quantity_required
              FROM mini_task_detail mtd
                       LEFT JOIN mini_task mt ON mtd.mini_task_id = mt.id
                       LEFT JOIN project_demand pd on mt.project_demand_id = pd.id
              WHERE mtd.is_deleted = 0
                AND mtd.type = 6
                AND mtd.sys_user_id = #{userId}
                HAVING 1
                ORDER BY mtd.deliver_time DESC
              ) mtd
        GROUP BY mtd.miniTaskId