如何在一个表中使用条件，在另一个表中更新记录？
如何在一个表中使用条件，在另一个表中更新记录？
提问于 2017-09-22 17:18:59
CREATE TABLE candidate_subjects (
id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
candidate_id INT(11),
exam_type_id INT(10),
subject_id INT(10),
ca_score INT(11),
exam_score INT(6),
score_grade VARCHAR(10),
date_created VARCHAR(10),
date_modified TIMESTAMP
);
INSERT INTO `candidate_subjects` (`id`, `candidate_id`, `exam_type_id`,
`subject_id`, `ca_score`, `exam_score`, `score_grade`, `date_created`,
`date_modified`) VALUES
(1, 2, 1, 32, 22, 61, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(2, 2, 1, 5, 21, 38, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(3, 2, 1, 14, 21, 51, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(4, 2, 1, 1, 19, 34, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(5, 2, 1, 2, 23, 39, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(6, 2, 1, 38, 20, 32, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(7, 2, 1, 53, 24, 47, NULL, '2017-02-01', '2017-08-28 13:10:33'),
(8, 4, 1, 32, 19, 61, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(9, 4, 1, 5, 22, 41, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(10, 4, 1, 14, 20, 46, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(11, 4, 1, 1, 23, 37, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(12, 4, 1, 2, 21, 36, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(13, 4, 1, 38, 22, 34, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(14, 4, 1, 53, 24, 52, NULL, '2017-02-01', '2017-08-28 13:11:27'),
(15, 5, 1, 32, 20, 62, NULL, '2017-02-01', '2017-08-28 13:11:44'),
(16, 5, 1, 5, 22, 38, NULL, '2017-02-01', '2017-08-28 13:11:44');
CREATE TABLE candidates (
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
exam_no VARCHAR(15),
surname VARCHAR(50),
other_names VARCHAR(100),
school_id INT(11),
registration_completed INT(11),
exam_scores_completed INT(5),
remark VARCHAR(10)
);
INSERT INTO candidates (id, exam_no, surname, other_names, school_id,
registration_completed, exam_scores_completed, remark) VALUES
(1, '1171052001', 'ABADO', 'MASENENGEN', 1052, 1, '1', ''),
(2, '1170938001', 'AGBA', 'NGUHER', 938, 1, '1', ''),
(3, '1170071001', 'ABEE', 'SESUUR', 71, 1, '1', ''),
(4, '1170938002', 'AHEN', 'REBECCA DOOSUUN', 938, 1, '1', '');在上面，我有一个表候选人和另一个表candidate_subjects，分别存储候选人详细信息和候选人分数。candidate_subjects有科目的候选分数。注:数学科目ID为2，英语Id为1，及格分数为40，即ca_score + exam_score总分。考生备注的条件是(数学<40分或英语<40分)注释是‘重试’。如果(数学>= 40和eng >= 40)和总通过>=6评分(数学分数<40分，eng <40分)，总通过<6分的科目(数学> 40和eng > 40)和总通过<6分(数学> 40和eng> 40)的总科目都不及格。
下面是我编写的查询，但没有给出预期的结果：
UPDATE candidates SET candidates.remark='FAIL' WHERE (select
count(candidate_subjects.id) AS total_pass from candidates,
candidate_subjects where candidates.id=candidate_subjects.candidate_id
and (candidate_subjects.ca_score + candidate_subjects.exam_score) >= 40) < 6回答 3
Stack Overflow用户
回答已采纳
发布于 2017-09-23 10:52:05
要根据您的标准更新候选人备注列，它可以是一个复杂的查询，如
update candidates c
join (
select a.candidate_id,a.total_pass,
e.ca_score + e.exam_score as english,
m.ca_score + m.exam_score as maths
from (select
count(id) AS total_pass ,candidate_id
from candidate_subjects
where(ca_score + exam_score) >= 40
group by candidate_id
) a
left join candidate_subjects e on (a.candidate_id = e.candidate_id
and e.subject_id = 1) /* english id */
left join candidate_subjects m on (a.candidate_id = m.candidate_id
and m.subject_id = 2) /* maths id */
) t on c.id = t.candidate_id
set c.remark = case
when t.maths < 40 or t.english < 40 then 'RESIT'
when t.maths >= 40 and t.english >= 40 and t.total_pass >= 6 then 'PASS'
when t.maths < 40 and t.english < 40 and t.total_pass < 6 then 'FAIL'
when t.maths > 40 and t.english > 40 and t.total_pass < 6 then 'FAIL'
else c.remark
end;演示
选择ids
一些注意事项确保您的模式中有以下索引
- 应将candidate_id中的candidate_subjects设置为对候选表的外键引用，并对其进行索引。
- 在(ca_score + exam_score)上添加复合索引
- 为subject_id添加索引
如果仍然面临长时间执行问题，那么运行join(....)部件添加之类的批处理中断查询
order by a.candidate_id asc
limit start,end // set these like 0,10000, 10000,10000, 20000,10000 .....限制
Stack Overflow用户
发布于 2017-09-22 17:38:55
什么在哪？当您为WHERE条件使用子subselect时，请将它看作是一个IN()子句。返回的值需要是条件逻辑中使用的值，在本例中是FK/ID。
你的查询的最后一部分对我来说也没有意义。这有道理吗？
UPDATE candidates SET candidates.remark = 'FAIL'
WHERE candidates.id IN
(SELECT candidate_id FROM
(SELECT candidate_id, count(candidate_subjects.id) AS total_pass
FROM candidates, candidate_subjects
WHERE candidates.id = candidate_subjects.candidate_id
AND (candidate_subjects.ca_score + candidate_subjects.exam_score) >= 40)
GROUP BY candidate_id
HAVING total_pass = 6);尽管认为存在一些语法错误，但希望它能有所帮助。那些子项可能需要别名..。(...) AS sub1,。记不起来了。
我会反向处理这个问题，创建一个返回要更新的正确candidate.id值的查询。然后，通过SQL测试和运行，将其添加到您的更新中：
UPDATE candidates SET candidates.remark = 'FAIL'
WHERE candidates.id IN (**THE SQL YOU WROTE AND TESTED RETURNING THE CORRECT IDS**)Stack Overflow用户
发布于 2017-09-22 18:24:12
使用简单的子查询。
UPDATE candidates
SET candidates.remark='FAIL'
WHERE (
SELECT count(*) from candidate_subjects
WHERE candidate_id=candidates.id and (candidate_subjects.ca_score + candidate_subjects.exam_score) >= 40)
) < 6页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46370028
复制相关文章
相似问题
腾讯云开发者
