首页 > 数据库技术 > 详细

Mysql在union all 之后order by 失效

时间:2021-08-29 19:13:14      阅读:16      评论:0      收藏:0      [点我收藏+]
//不正常的sql
SELECT
    *
FROM
    (
        (
        SELECT
            `class`.`id` as class_id,
            `class`.`name` as class_name,
            `class`.`start_time` AS `startTime`,
            `class_group`.`group_name`,
            `class_group`.`id` AS group_id 
        FROM
            `class`
            LEFT JOIN class_group ON `class_group`.`class_id` = `class`.`id` 
        WHERE
            `class`.`app_id` = 007cd3983760 
            AND `class_group`.`app_id` = 007cd3983760 
            AND `class_group`.`deleted_at` IS NULL 
            AND `class`.`deleted_at` IS NULL 
            AND class.start_time >= 2021-08-03 
            AND class.start_time <= 2021-08-20 
            order by class.name asc,group_id asc
        ) UNION all
        (
        SELECT
            `class`.`id` as class_id,
            `class`.`name` as class_name,
            `class`.`start_time` AS `startTime`,
            ,
          0
        FROM
            class 
        WHERE
            deleted_at IS NULL 
            AND app_id = 007cd3983760 
            AND class.start_time >= 2021-08-03 
            AND class.start_time <= 2021-08-20 
        ) 
    ) AS class_group_list 
ORDER BY
class_name ASC 
    LIMIT 1,
    20

不正常的结果,class_name排序是对了,但是最后一列的group_id不对,按说是group_id = 0的在每个class_name相同组的最前面

 

技术分享图片

 

解决问题:

//有效的sql
    select * from (    
        SELECT
            `class`.`id` as class_id,
            `class`.`name` as class_name,
            `class`.`start_time` AS `startTime`,
            ,
          0  as group_id
        FROM
            class 
        WHERE
            deleted_at IS NULL 
            AND app_id = 007cd3983760 
            AND class.start_time >= 2021-08-03 
            AND class.start_time <= 2021-08-20
UNION all
SELECT
            `class`.`id` as class_id,
            `class`.`name` as class_name,
            `class`.`start_time` AS `startTime`,
            `class_group`.`group_name`,
            `class_group`.`id` AS group_id 
        FROM
            `class`
            LEFT JOIN class_group ON `class_group`.`class_id` = `class`.`id` 
        WHERE
            `class`.`app_id` = 007cd3983760 
            AND `class_group`.`app_id` = 007cd3983760 
            AND `class_group`.`deleted_at` IS NULL 
            AND `class`.`deleted_at` IS NULL 
            AND class.start_time >= 2021-08-03 
            AND class.start_time <= 2021-08-20 
         ) cc order by class_name asc ,group_id asc

有效的结果

技术分享图片

 

Mysql在union all 之后order by 失效

原文:https://www.cnblogs.com/wt645631686/p/15194012.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!