MySQL 求和与大小写

Question

我正在创建一个查询，其中我将计算申请人拥有多少个奖项。到目前为止我有这个:

SELECT 
CASE WHEN Award1 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award2 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award3 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award4 IS NOT NULL THEN 1 ELSE 0 END 
as summedColumn
FROM resume, person
where E_Status = 'Applicant' 
and person.ID_No like 'x' and resume.ID_No like 'x'

Table:Person                         Values
ID_No(Varchar, Primary)              x
F_Name(Varchar)                      Fasa
L_Name(Varchar)                      Bel
M_Name(Varchar)                      Drake
Resume_ID(Varchar)                   res01

Table: Resume                        Value
Resume_ID(Varchar, Primary)          res01
ID_No(Varchar)                       x
Award1(Varchar)                      Suma Cum Laude
Award2(Varchar)                      null
Award3(Varchar)                      null
Award4(Varchar)                      null
Past_Position1(Varchar)              HR manager
Past_Position2(Varchar)              null

Output of the query: 4

当我运行代码时，它返回值 4，但我的 Award2、Award3 和 Award4 均为空。该代码假设返回值 1。

这是表格的外观:

Answer 1

您没有连接两个表，因此它在 Resume 和 Person 表之间进行交叉连接。

找到将 Person 与 Resume 相关联的键，并将它们相等:

SELECT 
CASE WHEN Award1 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award2 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award3 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN Award4 IS NOT NULL THEN 1 ELSE 0 END 
as summedColumn
FROM resume 
    INNER JOIN person
        ON resume.Resume_ID = person.Resume_ID
where E_Status = 'Applicant' 
and person.ID_No like 'x' and resume.ID_No like 'x'