如何在一个表中使用条件并更新另一表中的记录 [英] How do I use conditions in one table and update record in another table

问题描述

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)评论的总分是RESIT".else if (math >= 40 and eng >= 40) 并且总科目通过>=6 备注是'PASS' else if(数学分数<40并且工程分数<40)并且总科目通过<;6 备注是失败"，否则如果(数学 > 40 和 eng > 40)并且总科目通过 <;6 评论是 FAIL.

In above, I have a table candidates and another table candidate_subjects, for storing candidate details and candidate scores respectively. candidate_subjects have candidates score for subjects. Note subject ID for maths is 2, english Id is 1. Pass mark is 40, that is total of ca_score + exam_score. Condition for candidate remark is if total score for (math is < 40 or english is < 40) remark is 'RESIT'. else if (math >= 40 and eng >= 40) and total subjects passed >=6 remark is 'PASS' else if (score in math < 40 and score in eng < 40) and total subjects passed < 6 remark is 'FAIL' else if (math > 40 and eng > 40) and total subjects passed < 6 remark is FAIL.

下面是我写的查询，但没有给出预期的结果:

Below is the query I have written but it is not giving the result expected:

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

推荐答案

要根据您的条件更新候选人备注栏，这可能是一个复杂的查询

To update candidates remark column according to your criteria it can be a complex query as

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;

演示

SELECT *

选择 ID

一些注意事项确保您的架构中有以下索引

Some considerations make sure you have following indexes in your schema

• Candidate_subjects 中的candidate_id 应设置为对候选表的外键引用并应编入索引

• candidate_id in candidate_subjects should be set as a foreign key reference to candidates table and should be indexed

在 (ca_score +exam_score) 上添加复合索引

Add composite index on (ca_score + exam_score)

为 subject_id 添加索引

Add index for subject_id

如果您仍然面临长时间执行的问题，那么像 join(....) part add

If still you face long execution issue then run break your query in batches like in join(....) part add

order by a.candidate_id asc
limit start,end // set these like 0,10000, 10000,10000, 20000,10000 .....

限制

这篇关于如何在一个表中使用条件并更新另一表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！