mysql - 如何更新查询两张表并更新一张表？

Question

我有两个表，candidates 和 candidate_subjects，分别用于存储候选人详细信息和候选人分数。如果考生通过的科目少于 6 门，我想要一个查询将考生备注更新为“失败”。要通过某门科目，该科目的 ca_score 和 exam_score 考生总分必须大于 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

表格:

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', '');

Answer 1

要根据您的条件更新您的候选人，并且他们的记录存在于 candidate_subjects 中，您可以使用以下查询。

UPDATE candidates c
JOIN (select 
    count(id) AS total_pass ,candidate_id
    from candidate_subjects 
    where(ca_score + exam_score) >= 40
    group by candidate_id
     ) a on c.id = a.candidate_id
SET c.remark='FAIL' 
WHERE total_pass < 6 

DEMO