修复串列了的记录

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2021-01-01 10:00:00
2	9002	算法	hard	80	2021-01-01 10:00:00
3	9003	SQL	medium	70	2021-01-01 10:00:00
4	9004	算法,medium,80		0	2021-01-01 10:00:00

录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。

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

exam_id	tag	difficulty	duration
9004	算法	medium	80

示例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_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
  (9004, '算法,medium,80','', 0, '2020-01-01 10:00:00');

输出:

9004|算法|medium|80

原站题解

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

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

select exam_id
,substring_index(tag,',',1) tag
,substring_index(substring_index(tag,',',2),',',-1) difficulty
,substring_index(tag,',',-1) duration
from examination_info
where difficulty=''

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

select exam_id,
substring_index(tag,',',1) as tag,
substring_index(substring_index(tag,',',-2),',',1) as difficulty,
substring_index(tag,',',-1) as duration
from(
select * from examination_info
where tag like'%,%') a;

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

select exam_id,substring_index(tag,",",1) tag,
substring_index(substring_index(tag,",",2),",",-1) difficulty,
substring_index(tag,",",-1) duration
from examination_info
where tag like "%,%"

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

select exam_id,SUBSTRING_INDEX(tag,',',1),
    SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1),
    SUBSTRING_INDEX(tag,',',-1)
from examination_info e
where tag like '%,%'

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

select exam_id,
     SUBSTRING_INDEX(tag,',',1) tag, 
     SUBSTRING_INDEX(substring_index(tag,',',2),',',-1) difficulty,
     substring_index(tag,',',-1) duration
from examination_info 
where tag like'%,%'

上一题

下一题

详情