法一:
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;
知识点是UNION后的排序问题,ORDER BY子句只能在最后一次使用。 如果想要在UNION之前分别单独排序,那么需要这样:
SELECT * FROM
( SELECT * FROM t1 ORDER BY 字段 ) newt1 ## 一定要对表重新命名,否则报错
UNION
SELECT * FROM
( SELECT * FROM t2 ORDER BY 字段 ) newt2
法二:用tid字段的左边第一个数来排序。
SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,
COUNT(*) pv FROM exam_record
GROUP BY exam_id
UNION
SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,
COUNT(*) pv FROM practice_record
GROUP BY question_id
ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC;