mysql - MySQL的总和和大小写

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

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。

这是表格的外观：

最佳答案

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

找到将人与简历相关联的密钥，并将其等同起来：

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'