描述
现有试卷作答记录表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
#在union之前单独排序,需要对表重新命名
select * from
(select
exam_id as tid,count(distinct uid) as uv,count(start_time) as pv
from
exam_record
group by exam_id
order by pv desc,uv desc)t1
union
select * from
(select
question_id as tid,count(distinct uid) as uv,count(submit_time) as pv
from
practice_record
where submit_time is not null
group by question_id
order by pv desc,uv desc)t2