分别满足两个活动的人

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	easy	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 10:00:00

试卷作答记录表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:31:00	81
2	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	70
3	1003	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	86
4	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	89
5	1004	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85

示例数据输出结果：

uid	activity
1001	activity2
1003	activity1
1004	activity1
1004	activity2

解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2。

示例1

输入:

drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime 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 examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

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:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85);

输出:

1001|activity2
1003|activity1
1004|activity1
1004|activity2

原站题解

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

Mysql 解法, 执行用时: 36ms, 内存消耗: 6464KB, 提交时间: 2022-01-22

(select r.uid, 'activity1' as activity
from exam_record r
where year(submit_time) = 2021 
group by r.uid
having min(score) >= 85)
union all
(select r.uid,'activity2' as activity 
from exam_record r
left join examination_info i on r.exam_id = i.exam_id
where year(submit_time) = 2021 and i.difficulty = 'hard' and score >=80 and 
            timestampdiff(second,r.start_time,r.submit_time)<= i.duration*30
group by r.uid)
order by uid

Mysql 解法, 执行用时: 36ms, 内存消耗: 6592KB, 提交时间: 2022-01-26

SELECT uid, 'activity1' as activity
FROM exam_record
WHERE YEAR(start_time)=2021
GROUP BY uid
HAVING MIN(score)>=85
 
UNION ALL
 
SELECT DISTINCT uid, 'activity2' as activity
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE YEAR(start_time)=2021 and difficulty='hard' and score>80
    and TimeStampDiff(SECOND, start_time, submit_time)<=duration*30
ORDER BY uid;

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

SELECT er1.uid, 'activity1' AS activity 
FROM exam_record er1 
WHERE  YEAR(submit_time) = 2021
GROUP BY er1.uid
HAVING MIN(score) >= 85 

UNION ALL

SELECT er2.uid, 'activity2' AS activity
FROM exam_record er2
JOIN examination_info ei2 ON er2.exam_id = ei2.exam_id
WHERE ei2.difficulty = 'hard' AND TIMESTAMPDIFF(MINUTE,er2.start_time, er2.submit_time) <= 29 AND er2.score > 80
ORDER BY uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2022-01-25

(select e1.uid , "activity1" as activity
from exam_record e1
where YEAR(start_time) = 2021
group by e1.uid
having min(e1.score)>=85)
union aLL
(select uid , "activity2" as activity
from exam_record e1
left join examination_info e2
on e1.exam_id=e2.exam_id
WHERE YEAR(start_time) = 2021
and e1.score > 80
and e2.difficulty = "hard"
and TIMESTAMPDIFF(SECOND,e1.start_time,e1.submit_time) <= e2.duration*30
group by uid)
order by uid

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

(select uid,"activity1" as activity from exam_record
group by uid
having min(score)>=85)

UNION

(select distinct uid,"activity2" as activity from exam_record as  a
join (select * from examination_info
      where difficulty="hard") as  b
on a.exam_id=b.exam_id
# where TIMESTAMPDIFF(MINUTE,a.start_time,a.submit_time)<30
where score>80 and
YEAR(start_time)=2021 and
TIMESTAMPDIFF(SECOND,a.start_time, a.submit_time) <= (b.duration*30))

order by uid;
# select TIMESTAMPDIFF(MINUTE,start_time,submit_time) from exam_record

# select e1.uid,"activity2" as activity
# from exam_record as e1
# left join examination_info as e2
# on e1.exam_id=e2.exam_id
# where TIMESTAMPDIFF(MINUTE,e1.start_time, e1.submit_time) <= (e2.duration/2)

上一题

下一题

详情