Mysql笔记：UNION和ORDER BY 同时使用的问题

Mysql笔记：UNION和ORDER BY 同时使用的问题

例子

有以下几种情况

使用union和多个order by不加括号，报错！

SELECT exam_id AS tid,
       COUNT(DISTINCT UID) AS uv,
       COUNT(UID) AS pv
FROM exam_record
GROUP BY exam_id
ORDER BY uv DESC,
         pv DESC
UNION
SELECT question_id AS tid,
       COUNT(DISTINCT UID) AS uv,
       COUNT(UID) AS pv
FROM practice_record
GROUP BY question_id
ORDER BY uv DESC,
         pv DESC

直接报语法错误，如果没有括号，只能有一个order by

order by在union连接的子句中不起作用，但是能在子句的子句中起作用；

解决方案

这里的解决方案就是在外面再套一层查询

SELECT *
FROM
  (SELECT exam_id AS tid,
          COUNT(DISTINCT exam_record.uid) uv,
          COUNT(*) pv
   FROM exam_record
   GROUP BY exam_id
   ORDER BY uv DESC, pv DESC) t1
UNION
SELECT *
FROM
  (SELECT question_id AS tid,
          COUNT(DISTINCT practice_record.uid) uv,
          COUNT(*) pv
   FROM practice_record
   GROUP BY question_id
   ORDER BY uv DESC, pv DESC) t2;

UP BY question_id
ORDER BY uv DESC, pv DESC) t2;