mysql - 在带有或不带 HAVING 的 AND、OR 或 NOT 条件下在 mysql 中使用 Count？

Question

您好，我有表"users" 和"jobs"，其中包含以下数据

用户

+------+---------------+-----------+---------+---------------------+
| id   | first_name    | last_name | role_id | created             |
+------+---------------+-----------+---------+---------------------+
| 1026 | Administrator | Larvol    |       2 | 2014-07-25 22:28:21 |
|   20 | Worker        | Larvol    |       3 | 2014-07-24 20:14:18 |
|   22 | test          | user      |       3 | 2014-07-25 16:06:27 |
+------+---------------+-----------+---------+---------------------+

工作

+----+--------+---------+
| id | status | user_id |
+----+--------+---------+
|  1 |      3 |      20 |
|  2 |      4 |      22 |
+----+--------+---------+

到目前为止我从表中获取数据所做的是

SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ) )-SUM( IF( status = 1, 1, 0 ) ))) AS  JobsReviewed 
FROM alpha_dev.users AS Worker LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
WHERE Worker.role_id = 3 
GROUP BY Worker.id;

我得到的结果是

+----+------------+-----------+---------+---------------------+------------+--------------+
| id | first_name | last_name | role_id | created             | JobsAmount | JobsReviewed |
+----+------------+-----------+---------+---------------------+------------+--------------+
| 20 | Worker     | Larvol    |       3 | 2014-07-24 20:14:18 |          1 |            1 |
| 22 | test       | user      |       3 | 2014-07-25 16:06:27 |          1 |            1 |
+----+------------+-----------+---------+---------------------+------------+--------------+

现在我想在 "(COUNT( NULLIF(Job.id, 0) )) AS JobsAmount," 上创建一个 OR 条件，类似

WHERE Worker.role_id = 3 OR (COUNT( NULLIF(Job.id, 0) )) = 1

但那是行不通的，所以我最终得到了 HAVING 子句

WHERE Worker.role_id = 3 
GROUP BY Worker.id
HAVING COUNT(NULLIF(`Job`.`id`, 0)) = 0;

HAVING 在这里用作 AND 条件。并给了我 EMPTY SET，而我希望条件必须作为 OR 条件工作，所以要么

Worker.role_id = 3 OR COUNT(NULLIF(`Job`.`id`, 0)) = 0

应该是真的才能把事情做好。

如有任何帮助，我们将不胜感激。谢谢

Answer 1

如果 Job 中没有关联行，则表达式 COUNT(NULLIF(Job.id, 0)) 将为零。

检查 Job 中是否没有关联行的另一种方法是查看 Job 中的必需列是否为空。我说必填列是因为可选列无论如何都可能为空，而必填列只有在左联接表中没有任何匹配项时才会为空。

更具体地说，在您的特定场景中，我很确定 COUNT(NULLIF(Job.id, 0)) 与 Job.user_id IS NULL 的条件相同>。因为表达式 Job.user_id IS NULL 不是基于聚合，所以它可以进入 WHERE 而不是 HAVING:

• role=3 或 job count = 0

  SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ))-SUM( IF( status = 1, 1, 0 ) ))) AS JobsReviewed 
  FROM alpha_dev.users AS Worker
  LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
  WHERE Worker.role_id = 3 OR Job.user_id IS NULL
  GROUP BY Worker.id
  

这仅适用于 COUNT=0。如果您需要检查 COUNT=2 或 COUNT=5 或其他任何内容，您需要将现有查询推送到子查询中，然后让外部查询应用 OR 逻辑:

• role=3 OR job count = 5

  SELECT * FROM (
      SELECT Worker.id, 
        first_name, 
        last_name, 
        role_id, 
        Worker.created, 
        (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
        ((SUM(IF(status <> 0, 1, 0))-SUM( IF(status = 1, 1, 0)))) AS JobsReviewed 
      FROM alpha_dev.users AS Worker
      LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
      GROUP BY Worker.id
  ) WorkerSummary
  WHERE role_id = 3 OR JobsAmount = 5