mysql - 检查特定行上的值是否为 NULL

Question

我有以下查询，它查找计数:

stage_2 为空stage_1 等于 1成员组为 5按rel_id分组

其中至少有 2 行满足上述条件。

这很好用。它输出正确的结果 6。(在我的 fiddle 中)。

SELECT COUNT(*) AS count_result
FROM (
    SELECT sub.entry_id
    FROM exp_judging AS jud
    INNER JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
    WHERE jud.stage_2 IS NULL
    AND jud.stage_1 = 1 
    AND sub.member_group = 5
    GROUP BY jud.rel_id
    HAVING COUNT(*) >= 2
) AS a

问题是，如果评判行之一在“stage_2”中输入了分数，它会继续计算此 rel_id，因为 stage_2 中仍然有超过 2 行具有 NULL。

我想做的是检查上述所有内容是否正确，但同时，judge_id '14' 的行的 stage_2 为 NULL。

我怎样才能实现这个目标？

这是一个包含我的表和当前查询的 SQL Fiddle - http://sqlfiddle.com/#!2/e5ee5/1

Answer 1

考虑如下中间结果...

  SELECT s.id
      , s.member_group
      , j.judge_id
      , j.stage_1
      , j.stage_2
   FROM exp_judging j
   JOIN exp_submissions s
     ON s.id = j.rel_id
    AND j.stage_1 = 1
    AND s.member_group = 5
  ORDER
     BY j.rel_id
      , j.judge_id;

 +----+--------------+----------+---------+---------+
 | id | member_group | judge_id | stage_1 | stage_2 |
 +----+--------------+----------+---------+---------+
 | 70 |            5 |       14 |       1 |       5 |<-- exclude because [14,5]
 | 70 |            5 |       16 |       1 |    NULL |<-- exclude because of [14,5] above
 | 73 |            5 |       14 |       1 |       5 |<-- exclude because [14,5]
 | 73 |            5 |       15 |       1 |    NULL |<-- exclude because [14,5] above
 | 73 |            5 |       16 |       1 |    NULL |<-- exclude because [14,5] above
 | 75 |            5 |       15 |       1 |    NULL |
 | 75 |            5 |       16 |       1 |    NULL |
 | 77 |            5 |       15 |       1 |    NULL |
 | 77 |            5 |       16 |       1 |    NULL |
 | 78 |            5 |       16 |       1 |    NULL |
 | 79 |            5 |       14 |       1 |    NULL |
 | 80 |            5 |       14 |       1 |    NULL |
 | 80 |            5 |       15 |       1 |    NULL |
 | 80 |            5 |       16 |       1 |    NULL |
 | 81 |            5 |       16 |       1 |    NULL |
 | 83 |            5 |       14 |       1 |    NULL |
 | 83 |            5 |       15 |       1 |    NULL |
 +----+--------------+----------+---------+---------+

要排除突出显示的行，我们可以这样做...

  SELECT s.id
      , s.member_group
      , j.judge_id
      , j.stage_1
      , j.stage_2
   FROM exp_judging j
   JOIN exp_judging x
     ON x.rel_id = j.rel_id
    AND x.judge_id = 14
    AND x.stage_2 IS NULL
   JOIN exp_submissions s
     ON s.id = j.rel_id
    AND j.stage_1 = 1
    AND s.member_group = 5
  GROUP
     BY j.rel_id
      , j.judge_id;

 +----+--------------+----------+---------+---------+
 | id | member_group | judge_id | stage_1 | stage_2 |
 +----+--------------+----------+---------+---------+
 | 75 |            5 |       15 |       1 |    NULL |
 | 75 |            5 |       16 |       1 |    NULL |
 | 77 |            5 |       15 |       1 |    NULL |
 | 77 |            5 |       16 |       1 |    NULL |
 | 78 |            5 |       16 |       1 |    NULL |
 | 79 |            5 |       14 |       1 |    NULL |
 | 80 |            5 |       14 |       1 |    NULL |
 | 80 |            5 |       15 |       1 |    NULL |
 | 80 |            5 |       16 |       1 |    NULL |
 | 81 |            5 |       16 |       1 |    NULL |
 | 83 |            5 |       14 |       1 |    NULL |
 | 83 |            5 |       15 |       1 |    NULL |
 +----+--------------+----------+---------+---------+

从这里开始，剩下的步骤就变得微不足道了......

 SELECT s.id
      , COUNT(*)
   FROM exp_judging j
   JOIN exp_judging x
     ON x.rel_id = j.rel_id
    AND x.judge_id = 14
    AND x.stage_2 IS NULL
   JOIN exp_submissions s
     ON s.id = j.rel_id
    AND j.stage_1 = 1
    AND s.member_group = 5
  GROUP
     BY j.rel_id;

+----+----------+
| id | COUNT(*) |
+----+----------+
| 75 |        2 |
| 77 |        2 |
| 78 |        1 |
| 79 |        1 |
| 80 |        3 |
| 81 |        1 |
| 83 |        2 |
+----+----------+