每个题目和每份试卷被作答的人数和次数

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

输入:

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);

输出:

9001|3|3
9002|1|3
8001|3|5
8002|2|2

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6400KB, 提交时间: 2021-12-27

SELECT exam_id as tid, 
  count(distinct uid) as uv,
  count(uid) as pv
from exam_record
group by exam_id
union ALL
SELECT question_id as tid,
  count(distinct uid) as uv,
  count(uid) as pv
from practice_record
group by question_id
order by LEFT(tid,1) DESC,uv DESC,pv DESC;

Mysql 解法, 执行用时: 36ms, 内存消耗: 6408KB, 提交时间: 2021-12-12

select * FROM
(select exam_id,COUNT(DISTINCT uid) as uv, COUNT(*) as pv
from exam_record
GROUP BY exam_id
order by uv desc, pv desc) a 
UNION
select * FROM
(select question_id,COUNT(DISTINCT uid) as uv, COUNT(*) as pv
from practice_record pr
GROUP BY question_id
order by uv desc, pv desc) b

Mysql 解法, 执行用时: 36ms, 内存消耗: 6408KB, 提交时间: 2021-12-08

select * from (SELECT exam_id tid, count(distinct uid) uv, count(uid) pv
from exam_record
group by exam_id
order by uv desc, pv desc) a
UNION
select * from (SELECT question_id tid, count(distinct uid) uv, count(uid) pv
from practice_record
group by question_id
order by uv desc, pv desc) b

Mysql 解法, 执行用时: 36ms, 内存消耗: 6420KB, 提交时间: 2021-12-12

select * 
from 
(
SELECT exam_id tid, count(distinct uid) uv, count(uid) pv
from exam_record
group by exam_id
order by uv desc, pv desc
) a
UNION all
select * 
from 
(
SELECT question_id tid, count(distinct uid) uv, count(uid) pv
from practice_record
group by question_id
order by uv desc, pv desc
) b

Mysql 解法, 执行用时: 36ms, 内存消耗: 6468KB, 提交时间: 2021-11-30

#union all 
SELECT exam_id tid,count(distinct uid) uv , count(*) pv
from exam_record
group by exam_id

UNION all
SELECT question_id tid,count(distinct uid) uv , count(*) pv
from practice_record
group by question_id
order by LEFT(tid, 1) desc,uv desc,pv desc

上一题

下一题

详情