描述
现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
iduidexam_idstart_timesubmit_timescore1100190012021-09-01 09:01:012021-09-01 09:41:01812100290022021-09-01 12:01:012021-09-01 12:31:01703100290012021-09-01 19:01:012021-09-01 19:40:01804100290022021-09-01 12:01:012021-09-01 12:31:01705100490012021-09-01 19:01:012021-09-01 19:40:01856100290022021-09-01 12:01:01(NULL)(NULL)
题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：
iduidquestion_idsubmit_timescore1100180012021-08-02 11:41:01602100280012021-09-02 19:30:01503100280012021-09-02 19:20:01704100280022021-09-02 19:38:01705100380012021-08-02 19:38:01706100380012021-08-02 19:48:01907100380022021-08-01 19:38:0180
请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：
tiduvpv900133900213800135800222
解释：“试卷”有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
精彩文章
发表评论