上sql
select * FROM (
SELECT
SUM(c.overtime_num) AS delay_num,
ROUND((SUM(c.total_num) - SUM(c.overtime_num))*100/SUM(c.total_num),2) rate ,
'全网' as reaCodeFROM calc_vmap_repair_timely_rate_mon_stat c
WHERE c.`type` = 22
and c.MONTH BETWEEN '2019-01' AND '2019-01'
) t1
UNION ALL
SELECT t2.* FROM (
select tmp.* FROM (
SELECT SUM(c.overtime_num) AS delay_num,
ROUND((SUM(c.total_num) - SUM(c.overtime_num))*100/SUM(c.total_num),2) rate ,
c.motorcade_area_code as reaCodeFROM calc_vmap_repair_timely_rate_mon_stat c
WHERE c.`type` = 22
and c.MONTH BETWEEN '2019-01' AND '2019-01'
group by c.motorcade_area_code
) tmp order by tmp.rate asc
) t2
单独执行union all下面的结果如下:
真正解决方案:
SELECT * FROM(
SELECT
SUM(c.overtime_num) AS delay_num,
ROUND((SUM(c.total_num) - SUM(c.overtime_num))*100/SUM(c.total_num),2) rate ,
'全网' AS reaCode,
0 AS od
FROM calc_vmap_repair_timely_rate_mon_stat c
WHERE c.`type` = 22
and c.MONTH BETWEEN '2019-01' AND '2019-01'
UNION ALL
SELECT SUM(c.overtime_num) AS delay_num,
ROUND((SUM(c.total_num) - SUM(c.overtime_num))*100/SUM(c.total_num),2) rate ,
c.motorcade_area_code AS reaCode,
1 AS od
FROM calc_vmap_repair_timely_rate_mon_stat c
WHERE c.`type` = 22
and c.MONTH BETWEEN '2019-01' AND '2019-01'
group by c.motorcade_area_code
) con ORDER BY od, rate;
先查询后排序
union 前的排序与union 后的顺序,采用加一个字段od来保证,然后再按rate排序则解决了以上的问题。