题解|含UNION排序|#每个题目每份试卷作答的人数和次数#

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

注意UNION 和ORDER BY同时存在时的用法

法一：

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;