MySQL - 仅当组中的所有内容均为 NULL 时才选择

Question

我的 table 是这样的:

ID|EID|priority|completed_forms|all_forms_in
1 |78 |NULL    |y              |y
2 |24 |NULL    |y              |y
3 |78 |1       |y              |y

我想要完成的任务:仅选择以下行:如果它们都共享相同的 EID 并且都共享 NULL 优先级，并且对于 Completed_forms 和 all_forms_in 标记为“y”。

我尝试过的:这是我当前的选择语句:

SELECT * FROM tasks 
WHERE priority IS NULL AND EID in 
(
SELECT * FROM 
    (select EID from tasks 
    WHERE completed_form = 'y' AND all_forms_in = 'y' ) as priority_check
GROUP BY EID having count(*) = 2
)

预期结果:我希望它不返回任何行，因为选择应确保仅当两行均为 NULL 时才返回。在本例中，ID 1 和 ID 3 并不都共享 NULL。相反，它返回 ID 1。

Answer 1

您可以添加 AND COUNT(priority) = 0(带有字段名称的 COUNT 不计算空值)，如下所示:

SELECT EID FROM tasks 
GROUP BY EID HAVING 
  COUNT(priority) = 0 AND
  COUNT(DISTINCT completed_form) = 1 AND MAX(completed_form) = 'y' AND
  COUNT(DISTINCT all_forms_in) = 1 AND MAX(all_forms_in) = 'y'AND
  COUNT(*) = 2