在Myql中
select 与order by 共同使用 ,要注意 如果order by不是在union all连接两个表的内层,这样的使用方法是错误的 !!!
如下图,这是我在牛客网《SQL132 每个题目和每份试卷被作答的人数和次数》中遇到的问题,就是union连接的两张表需要进行某些字段的排序
select
exam_id,
count(distinct uid) as uv,
count(*) as pv
from
exam_record
group by
exam_id
order by
count(distinct uid) desc,
count(*) desc
union all
select
question_id,
count(distinct uid) as uv,
count(*) as pv
from
practice_record
group by
question_id
order by
count(distinct uid) desc,
count(*) desc
这样是在将两张表先进行order by 之后 进行union all。
报错:
正确的方法是在union all 的子句中再套一层,让order by 在成为子句的子句!!!
select *
from
( select
exam_id,
count(distinct uid) as uv,
count(*) as pv
from
exam_record
group by
exam_id
order by
count(distinct uid) desc,
count(*) desc) t1
union all
select *
from
(select
question_id,
count(distinct uid) as uv,
count(*) as pv
from
practice_record
group by
question_id
order by
count(distinct uid) desc,
count(*) desc
) t2
ps:附题目和建表语句:
现有试卷作答记录表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降序显示,示例数据结果
drop table if exists practice_record;
CREATE TABLE practice_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
question_id int NOT NULL COMMENT '题目ID',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
drop table if exists exam_record;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8001, '2021-08-02 19:38:01', 70),
(1003, 8001, '2021-08-02 19:48:01', 90),
(1003, 8002, '2021-08-01 19:38:01', 80);
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-09-01 12:01:01', null, null);