大小写混乱时的筛选统计

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2021-01-01 10:00:00
2	9002	C++	hard	80	2021-01-01 10:00:00
3	9003	C++	hard	80	2021-01-01 10:00:00
4	9004	sql	medium	70	2021-01-01 10:00:00
5	9005	C++	hard	80	2021-01-01 10:00:00
6	9006	C++	hard	80	2021-01-01 10:00:00
7	9007	C++	hard	80	2021-01-01 10:00:00
8	9008	SQL	medium	70	2021-01-01 10:00:00
9	9009	SQL	medium	70	2021-01-01 10:00:00
10	9010	SQL	medium	70	2021-01-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	2020-01-01 09:01:01	2020-01-01 09:21:59	80
2	1002	9003	2020-01-20 10:01:01	2020-01-20 10:10:01	81
3	1002	9002	2020-02-01 12:11:01	2020-02-01 12:31:01	83
4	1003	9002	2020-03-01 19:01:01	2020-03-01 19:30:01	75
5	1004	9002	2020-03-01 12:01:01	2020-03-01 12:11:01	60
6	1005	9002	2020-03-01 12:01:01	2020-03-01 12:41:01	90
7	1006	9001	2020-05-02 19:01:01	2020-05-02 19:32:00	20
8	1007	9003	2020-01-02 19:01:01	2020-01-02 19:40:01	89
9	1008	9004	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1008	9001	2020-02-02 12:01:01	2020-02-02 12:31:01	98
11	1009	9002	2020-02-02 12:01:01	2020-01-02 12:43:01	81
12	1010	9001	2020-01-02 12:11:01	(NULL)	(NULL)
13	1010	9001	2020-02-02 12:01:01	2020-01-02 10:31:01	89

试卷的类别tag可能出现大小写混乱的情况，请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。

如果转换后tag并没有发生变化，不输出该条结果。

由示例数据结果输出如下：

tag	answer_cnt
C++	6

解释：被作答过的试卷有9001、9002、9003、9004，他们的tag和被作答次数如下：

exam_id	tag	answer_cnt
9001	算法	4
9002	C++	6
9003	c++	2
9004	sql	2

作答次数小于3的tag有c++和sql，而转为大写后只有C++本来就有作答数，于是输出c++转化大写后的作答次数为6。

示例1

输入:

drop table if exists examination_info,exam_record;
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_bin;

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, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'c++', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'sql', 'medium', 70, '2020-01-01 10:00:00'),
  (9005, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9006, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9007, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9008, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
  (9009, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
  (9010, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 80),
(1002, 9003, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 81),
(1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75),
(1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60),
(1005, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90),
(1006, 9001, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 20),
(1007, 9003, '2020-01-02 19:01:01', '2020-01-02 19:40:01', 89),
(1008, 9004, '2020-01-02 12:01:01', '2020-01-02 12:20:01', 99),
(1008, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 98),
(1009, 9002, '2020-01-02 12:01:01', '2020-01-02 12:43:01', 81),
(1010, 9002, '2020-01-02 12:11:01', null, null),
(1011, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89);

输出:

c++|6

原站题解

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

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

/*
#1. 所有tag 各自的 总答题数
select tag ,count(start_time) cnt
from examination_info ei
left join exam_record er on ei.exam_id=er.exam_id
group by tag
算法|4
C++|6
c++|2
sql|1
SQL|0
*/
/*
#2. 查询 作答次数小于3的tag
select tag
from exam_record er
join examination_info ei on er.exam_id=ei.exam_id
group by ei.exam_id
having count(start_time) <3

c++
sql
*/

#3.将表1和表2连接一起 通过大写的表2tag =表1 .tag
select a.tag , cnt
from (
    select tag
    from exam_record er
    join examination_info ei on er.exam_id=ei.exam_id
    group by ei.exam_id
    having count(start_time) <3
) a
join (
    select tag ,count(start_time) cnt
    from examination_info ei
    left join exam_record er on ei.exam_id=er.exam_id
    group by tag
) b 
on UPPER(a.tag)=b.tag
where cnt <> 0
and a.tag <> upper(a.tag)

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

with t as (
    select tag,
    count(er.start_time) as answer_cnt
    from examination_info ei right join exam_record er using (exam_id)
    group by tag
)

select t.tag,
t1.answer_cnt
from t inner join t t1 on upper(t.tag) = t1.tag and t.tag != t1.tag
where t.answer_cnt < 3

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

with a as (
    select er.exam_id,tag,count(start_time) as answer_cnt 
    from exam_record er 
    join examination_info ei on er.exam_id=ei.exam_id 
    group by tag
)

select a.tag,b.answer_cnt 
from a join a as b 
on upper(a.tag)=b.tag and a.tag!=b.tag 
where a.answer_cnt<3

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

WITH t_tag_count as (
    SELECT tag, COUNT(uid) as answer_cnt
    FROM exam_record
    LEFT JOIN examination_info USING(exam_id)
    GROUP BY tag
)
 
SELECT a.tag, b.answer_cnt
FROM t_tag_count as a
JOIN t_tag_count as b
ON UPPER(a.tag) = b.tag and a.tag != b.tag and a.answer_cnt < 3;

####

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

#筛选试卷作答次数小于3的类别tag
#将这tag转换为大写
#对应原本的试卷作答次数
#转换后，tag没变化，不输出该结果
with t as (
    select tag,count(start_time) as answer_cnt
    from exam_record er
    join examination_info ei on er.exam_id = ei.exam_id
    group by tag
    )
     
select t.tag,t2.answer_cnt
from t
inner join t as t2
on UPPER(t.tag) = t2.tag
and t.tag <> t2.tag
where t.answer_cnt < 3

上一题

下一题

详情