MySQL union 和 order by 同时使用

一、出现错误的情况
二、解决上述问题的两种方法
三、案例分析：

一、出现错误的情况

（1）使用 union 和多个order by 不加括号报错

（2）order by 在 union 连接的子句不起作用

二、解决上述问题的两种方法

（1）order by 在 union 连接的子句的子句中使用

（2）先使用 union 后使用order by

三、案例分析：

我们举个例子进行说明，案例来自：SQL132 每个题目和每份试卷被作答的人数和次数

描述

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-09-01 09:01:01	2021-09-01 09:41:01	81
2	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	70
3	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	80
4	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	70
5	1004	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	85
6	1002	9002	2021-09-01 12:01:01	(NULL)	(NULL)

题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8001	2021-08-02 19:38:01	70
6	1003	8001	2021-08-02 19:48:01	90
7	1003	8002	2021-08-01 19:38:01	80

请统计每个题目和每份试卷被作答的人数和次数，分别按照”试卷”和”题目”的uv & pv降序显示，示例数据结果输出如下：

tid	uv	pv
9001	3	3
9002	1	3
8001	3	5
8002	2	2

解释：“试卷”有3人共练习3次试卷9001，1人作答3次9002；“刷题”有3人刷5次8001，有2人刷2次8002。

求解：

分析思路

难点：

1.union 和 order by 同时使用需要注意的问题

（1）统计每份试卷被作答的人数和次数

[条件]：where score >= 85 and year(start_time) = 2021
[使用]：distinct。一定要去重

（2）统计每个题目被作答的人数和次数

[条件]：where difficulty = ‘hard’ and score > 80 and year(start_time) = 2021 and timestampdiff(minute, start_time, submit_time) < duration / 2
[使用]：多表连接使用 join using( )

（3）合并两个表,分别按照”试卷”和”题目”的uv & pv降序显示

[使用]：union all 和union 都可以，因为列activity不会有重复。

最终结果

select * from 
(
select 查询结果 [试卷ID; 作答次数]
from 从哪张表中查询数据[试卷作答记录表]
group by 分组条件 [试卷ID]
order by 对查询结果排序 [按照"试卷"的uv & pv降序]
)
union
select * from 
(
select 查询结果 [题目ID; 作答次数]
from 从哪张表中查询数据[题目练习表]
group by 分组条件 [题目ID]
order by 对查询结果排序 [按照"题目"的uv & pv降序]
)

常见的错误解法

（1）使用 union 和多个order by 不加括号【报错】

select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
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 b
group by question_id
order by uv desc, pv desc

执行出错

程序异常退出, 请检查代码”是否有数组越界等异常”或者”是否有语法错误”

SQL_ERROR_INFO: “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘union all\n\nselect \n question_id as tid,\n count(distinct uid) as uv,\n co’ at line 11”

这是因为union在没有括号的情况下只能有一个order by。为什么只能有一个order by 呢？

既然不加括号出错，那我就加上括号使用2个order by !

（2）order by 在 union 子句中不起作用

(select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
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 b
group by question_id
order by uv desc, pv desc)

结果发现这样写order by在每个子表中不起作用！所以这种方法也不对。但是 union 可以在子句的子句中起作用，写成下面这样：

正确解法

方法一：

（1）order by 在 union 连接的子句的子句中使用

#正确代码
select * from 
(
select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc
) a
union
select * from 
(
select 
    question_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc
) attr

那我是不是可以union两个子句之后再使用order by ，但是这个排序要对2个表分别进行降序，就需要写成下面这样：

方法二：

（2）先使用 union 后使用order by

使用函数

left(str,length) 函数： str左边开始的长度为 length 的子字符串，在本例中为‘9’和‘8’

解释：试卷编号以‘9’开头、题目编号以‘8’开头，对编号进行降序就是对”试卷”和”题目”分别进行排序

(
#每份试卷被作答的人数和次数
select
    exam_id as tid,
    count(distinct uid) as uv,
    count(*) as pv
from exam_record
group by exam_id
)
union
(
#每个题目被作答的人数和次数
select
    question_id as tid,
    count(distinct uid) as uv,
    count(*) as pv
from practice_record
group by question_id
)
#分别按照"试卷"和"题目"的uv & pv降序显示
order by left(tid,1) desc,uv desc,pv desc

推荐使用方法一，更具有普适性。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/98834.html