解决mysql union all 子查询排序失效办法

1.子查询排序生效

SELECT t.* FROM (
    SELECT t1.* FROM (
    SELECT * FROM `alarm_info`
    WHERE disposal_type='0'
   ORDER BY alarm_level ASC,create_time DESC) t1
    UNION ALL
    SELECT t2.* FROM (
    SELECT * FROM `alarm_info`
    WHERE disposal_type=1
    ORDER BY create_time DESC,alarm_level ASC) t2
    ) t

2.使用limit

SELECT t.* FROM (
    SELECT t1.* FROM (
    SELECT * FROM `alarm_info`
    WHERE disposal_type='0'
   ORDER BY alarm_level ASC,create_time DESC LIMIT 10) t1
    UNION ALL
    SELECT t2.* FROM (
    SELECT * FROM `alarm_info`
    WHERE disposal_type=1
    ORDER BY create_time DESC,alarm_level ASC  LIMIT 10) t2
    ) t

2.遇见分页接口，不能用limit ,则使用(@i:=@i+1)

SELECT t.* FROM (
    SELECT (@i:=@i+1) AS row_num,t1.* FROM (
    SELECT * FROM `alarm_info`
    WHERE disposal_type=0
    ORDER BY alarm_level ASC,create_time DESC) t1,(SELECT @i:=0) AS init
    UNION ALL
    SELECT (@i:=@i+1) AS row_num, t2.* FROM (
    SELECT * FROM `alarm_info`
    WHERE  disposal_type=1
    ORDER BY create_time DESC,alarm_level) t2,(SELECT @i:=0) AS init
    ) t

😂😂😂研究过order by case when 但是case when里面不支持多个字段排序，所以采用了自增长下标的方式,如果谁成功了call我下