使用LEFT JOIN(MySQL)时如何排除行 [英] How to exclude rows when using a LEFT JOIN (MySQL)

问题描述

我有个用户，其中有许多帖子.我想构建一个SQL查询，该查询将在1个查询(无子查询)中执行以下操作，并希望在可能的情况下不存在任何并集.我知道我可以用union来做到这一点，但我想了解是否可以仅使用联接来做到这一点.

I have users with many posts. I want to build an SQL query that would do the following in 1 query (no subquery), and hopefully no unions if possible. I know I can do this with union but I want to learn if this can be done using only joins.

我想获得以下不同活跃用户的列表:

1. 没有任何帖子
2. 没有批准的帖子

这是我到目前为止的内容:

SELECT DISTINCT u.*
FROM users u
  LEFT JOIN posts p
    ON p.user_id = u.id
  LEFT JOIN posts p2
    ON p2.user_id = u.id
WHERE u.status = 'active'
  AND (p.status IS NULL
  OR p2.status != 'approved');

问题是，当用户有多个帖子并且一个帖子处于活动状态时.这仍然会返回我不想要的用户.如果用户有活跃帖子，则应将其从结果集中删除.有什么想法吗?

The problem is when a user has multiple posts and one is active. This will still return the user which I do not want. If a user has an active post, he should be removed from the result set. Any ideas?

这是数据的样子:

mysql> select * from users;
+----+---------+
| id | status  |
+----+---------+
|  1 | active  |
|  2 | pending |
|  3 | pending |
|  4 | active  |
|  5 | active  |
+----+---------+
5 rows in set (0.00 sec)

mysql> select * from posts;
+----+---------+----------+
| id | user_id | status   |
+----+---------+----------+
|  1 |       1 | approved |
|  2 |       1 | pending  |
|  3 |       4 | pending  |
+----+---------+----------+
3 rows in set (0.00 sec)

这里的答案应该只有用户4和5.4没有批准的帖子，5没有帖子.它不应包含1个帖子，该帖子已获批准.

The answer here should be only users 4 and 5. 4 doesn't have an approved post and 5 doesn't have a post. It should not include 1, which has an approved post.

推荐答案

考虑您的需求并将其从字面上转换为SQL，我得到了:

Taking your requirements and translating them literally to SQL, I get this:

SELECT users.id,
       COUNT(posts.id) as posts_count,
       COUNT(approved_posts.id) as approved_posts_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
LEFT JOIN posts approved_posts
  ON approved_posts.status = 'approved'
  AND approved_posts.user_id = users.id
WHERE users.status = "active"
GROUP BY users.id
HAVING (posts_count = 0 OR approved_posts_count = 0);

对于上面的测试数据，将返回:

For your test data above, this returns:

4|1|0
5|0|0

即ID为 4 和 5 的用户，其中第一个具有1个帖子，但没有批准的帖子，而第二个则没有帖子.

i.e. users with ids 4 and 5, the first of which has 1 post but no approved posts and the second of which has no posts.

但是，在我看来，这可以简化，因为任何没有批准帖子的用户也将没有帖子，因此不需要条件合并.

However, it seems to me that this can be simplified since any user that has no approved posts will also have no posts, so the union of conditions is unnecessary.

在这种情况下，SQL就是:

In that case, the SQL is simply:

SELECT users.id,
       COUNT(approved_posts.id) as approved_posts_count
FROM users
LEFT JOIN posts approved_posts
  ON approved_posts.status = 'approved'
  AND approved_posts.user_id = users.id
WHERE users.status = "active"
GROUP BY users.id
HAVING approved_posts_count = 0;

这还将返回相同的两个用户.我想念什么吗?

This also returns the same two users. Am I missing something?

这篇关于使用LEFT JOIN(MySQL)时如何排除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！