首页 > 试题广场 >

更新记录（二）

[编程题]更新记录（二）

热度指数：81575 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：

submit_time为完成时间

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为0。

示例1

输入

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS 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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90),
(1002, 9001, '2021-08-02 19:01:01', null, null),
(1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1003, 9001, '2021-09-02 12:01:01', null, null),
(1003, 9002, '2021-09-01 12:01:01', null, null);

输出

1001|9001|2020-01-02 09:01:01|2020-01-02 09:21:01|80
1001|9002|2021-09-01 09:01:01|2021-09-01 09:21:01|90
1002|9001|2021-08-02 19:01:01|2099-01-01 00:00:00|0
1002|9002|2021-09-05 19:01:01|2021-09-05 19:40:01|89
1003|9001|2021-09-02 12:01:01|None|None
1003|9002|2021-09-01 12:01:01|None|None

算法知识视频讲解

Mysql

徐文达

UPDATE
    exam_record
SET
    submit_time = '2099-01-01 00:00:00',
    score = 0
WHERE
    submit_time IS NULL
    AND
    start_time < '2021-09-01';

发表于 2025-01-07 21:19:38 回复(0)

Mysql

Yaki呀

UPDATE exam_record
SET submit_time = '2099-01-01 00:00:00',
    score = 0
WHERE DATE(start_time) < '2021-09-01 00:00:00'
    AND submit_time IS NULL;

发表于 2024-11-21 17:22:15 回复(0)

Mysql

努力学习数分的一小只

update exam_record

set submit_time = '2099-01-01 00:00:00', score=0

where start_time < '2021-09-01 00:00:00',submit_time is null and score is null;

为啥这样不行啊？

发表于 2024-03-22 11:30:22 回复(1)

Mysql

想去深圳的小太阳爱健身

update exam_record set submit_time = replace(submit_time,'none','2099-01-01 00:00:00'),

score = replace(score,'none',0)

where start_time < '2021-09-01'

这个为啥不行？

发表于 2023-06-19 17:45:43 回复(0)

Mysql

保护发际线

update exam_record set submit_time = '2099-01-01 00:00:00', score = 0 where submit_time is null and start_time<'2021-09-01';

发表于 2023-05-10 10:51:06 回复(0)

Mysql

4fbd81e

update  exam_record
set submit_time="2099-01-01 00:00:00",score=0
where start_time<"2021-09-01" and score is null

发表于 2023-03-17 18:38:14 回复(0)

Mysql

做则必成的勇气和信心

update exam_record
set submit_time="2099-01-01 00:00:00",score=0  /*set用，连接*/
where date(start_time)<"2021-09-01 00:00:00" and score is null  /*where用and并列*/

发表于 2023-03-03 09:57:34 回复(0)

Mysql

酸菜鱼土豆大侠

【场景】：按照条件(批量)更新多值

【分类】：更新记录、update + if、update + case

分析思路

难点：

1.根据条件更新多值。值之间加逗号

方法一、根据指定条件更新

[使用]：利用where条件查找行后，对列名1字段中的值更新为值1

update 表名 set 列名1 = 值1 [,列名2=值2] [where 条件];

方法二、批量更新

使用 if 不同条件更新不同内容

update 表名 
set 
    列名1 = if(条件1,值1,值2),
    列名2 = if(条件2,值3,值4)
[where 条件];

使用 case when 不同条件更新不同内容

update 表名 
set 列名1 =
    case
        when 条件1 then 值1
        when 条件2 then 值2
        ...
    end,
    列名2 =
    case
        when 条件21 then 值21
        when 条件22 then 值22
        ...
    end
[where 条件];

扩展

前往查看:MySQL 更新数据

求解代码

方法一：

update + where

update exam_record 
set 
    submit_time = '2099-01-01 00:00:00',
    score = 0
where date(start_time) < date('2021-09-01') and score is null

方法二：

使用批量更新多值 + if

update exam_record 
set 
    submit_time = if(date(start_time) < date('2021-09-01') and score is null,'2099-01-01 00:00:00',submit_time),
    score = if(date(start_time) < date('2021-09-01') and score is null,0,score)

方法三：

使用批量更新多值 + case when

update exam_record 
set 
    submit_time = case
        when date(start_time) < date('2021-09-01') and score is null then '2099-01-01 00:00:00'
        else submit_time
    end,
    score = case
        when date(start_time) < date('2021-09-01') and score is null then 0
        else score
    end

发表于 2022-12-17 21:12:13 回复(1)

Mysql

京西府海棠

update exam_record
set submit_time='2099-01-01 00:00:00', score='0'
where DATE(start_time)<'2021-09-01' and submit_time is NULL

好无语，

score='0'

不加引号不让我过。。。加了才让过

发表于 2022-12-14 22:54:44 回复(0)

Mysql

枸杞姜枣茶

update exam_record set submit_time = '2099-01-01 00:00:00',score = '0' where start_time < '2021-09-01' and submit_time is null

发表于 2022-10-31 19:02:07 回复(0)

Mysql

muyuweimeng

UPDATE exam_record

SET submit_time = '2099-01-01 00:00:00', score = 0

WHERE start_time < '2021-09-01 00:00:00' AND submit_time IS NULL;

发表于 2022-10-29 17:43:51 回复(0)

Mysql

牛客599574273号

update
  exam_record
set
  submit_time = '2099-01-01 00:00:00',
  score = 0
where
  start_time < '2021-09-01 00:00:00'
  and submit_time is null

发表于 2022-09-24 17:40:14 回复(0)

Mysql

lss57

update exam_record set submit_time='2099-01-01 00:00:00' ,score=0
where submit_time is null and datediff(start_time,'2021-09-01')<0

发表于 2022-09-15 15:35:10 回复(0)

Mysql

喜欢走神的垂耳兔拥抱太阳

update exam_record
set submit_time='2099-01-01 00:00:00'
,score=0
where start_time<'2021-09-01 00:00:00'
and submit_time is null

发表于 2022-08-28 18:28:37 回复(0)

Mysql

一个莫名其妙的滑手

update exam_record 
set submit_time = '2099-01-01 00:00:00', score = 0
where start_time < '2021-09-01 00:00:00' and submit_time is null;

发表于 2022-08-22 23:07:33 回复(0)

Mysql

牛客369711270号

The question's wording is quite confusing. Both of the following solutions can pass the test:

update exam_record

set submit_time = '2099-01-01 00:00:00', score = 0

where start_time<'2021-09-01' and score is null

update exam_record
set submit_time = '2099-01-01 00:00:00', score = 0
where start_time<'2021-09-01' and submit_time is null

发表于 2022-08-19 07:03:07 回复(0)

Mysql

选钝角的小刺猬很纠结

-- 2021年9月1日之前开始作答的未完成记录全部改为被动完成，
-- 即：将完成时间改为'2099-01-01 00:00:00'，分数改为0。

update exam_record set submit_time = '2099-01-01 00:00:00' ,score = 0
where start_time < '2021-09-01 00:00:00' and submit_time is null;

发表于 2022-08-17 10:33:22 回复(0)