MySQL 三表链式左连接，按最后一张表过滤

Question

我正在尝试找到一种有效的方法来过滤掉由链接的三个表左连接生成的结果集，其中第二个表连接将考虑第三个表的属性。

fiddle :http://sqlfiddle.com/#!2/e319e/2/0

一个简化的示例是三个表之间的连接:帖子、评论和作者。帖子可以有 0..N 条由作者撰写的评论。我想获得所有帖子的列表 + 仅由活跃作者撰写的活跃评论。

考虑以下数据:

帖子:

| id |       title |
|----|-------------|
|  1 |  First post |
|  2 | Second post |
|  3 |  Third post |
|  4 |  Forth post |

作者:

| id |                  title | is_active |
|----|------------------------|-----------|
|  1 |           First author |         1 |
|  2 |          Second author |         1 |
|  3 |           Third author |         1 |
|  4 | Fourth inactive author |         0 |
|  5 |  Fifth inactive author |         0 |

评论:

| id | post_id | author_id |                  title | is_active |
|----|---------|-----------|------------------------|-----------|
|  1 |       1 |         1 |          First comment |         1 |
|  2 |       2 |         1 |         Second comment |         1 |
|  3 |       1 |         2 |          Third comment |         1 |
|  4 |       2 |         4 |         Fourth comment |         1 |
|  5 |       2 |         5 | Fifth inactive comment |         0 |
|  6 |       2 |         3 | Sixth inactive comment |         0 |
|  7 |       4 |         4 |        Seventh comment |         1 |

现在执行简单的过滤查询:

SELECT
  p.id post_id, p.title post_title,
  c.id as comment_id, c.title comment, c.is_active active_comment,
  a.id author_id, a.title author, a.is_active active_author
FROM Post p
LEFT JOIN Comment c ON c.post_id = p.id AND c.is_active = 1
LEFT JOIN Author a ON a.id = c.author_id AND a.is_active = 1
ORDER BY p.id;

给我们带来了以下结果集:

| id |       title |     id |           title | is_active |     id |         title | is_active |
|----|-------------|--------|-----------------|-----------|--------|---------------|-----------|
|  1 |  First post |      1 |   First comment |         1 |      1 |  First author |         1 |
|  1 |  First post |      3 |   Third comment |         1 |      2 | Second author |         1 |
|  2 | Second post |      2 |  Second comment |         1 |      1 |  First author |         1 |
|  2 | Second post |      4 |  Fourth comment |         1 | (null) |        (null) |    (null) |
|  3 |  Third post | (null) |          (null) |    (null) | (null) |        (null) |    (null) |
|  4 |  Forth post |      7 | Seventh comment |         1 | (null) |        (null) |    (null) |

有两条评论应该被省略——“第四条评论”和“第七条评论”，它们是由不活跃的作者写的。

我认为唯一可行的方法是为 Comment 添加 JOIN 条件

AND c.id IN (SELECT id FROM Author WHERE is_active = 1)

生成了正确的结果集，但我想这不是最优的。但是我找不到任何其他可行的解决方案。有没有办法以某种方式优化它？谢谢!

Answer 1

我想你想要这个 from 子句:

FROM Post p LEFT JOIN
     (Comment c JOIN
      Author a
      ON a.id = c.author_id AND a.is_active = 1 and c.is_active = 1
     )
     ON c.post_id = p.id

不过，正如我在评论中提到的，您可能希望停用不活跃作者的评论。这将涉及触发器或存储过程。

哦，你很有礼貌地提出了一个 SQL Fiddle，所以 here它正在工作。