sql - 如何查找子表(一对多)中哪些行至少有一个非空字段，哪些行只有空字段？

Question

假设我有一个数据库，其中包含:

• 包含帖子名称的表 post
• 包含部分帖子的表 part，主键字段链接到 post_id(一对多)
• comment 表，其中包含各个部分的注释，主键字段链接到 part_id(一对多)

想象一下查询:

SELECT *
FROM post
JOIN part ON post.id = part.post_id
JOIN comment ON part.id = comment.part_id

我们会得到例如结果:

|---------|-----------|---------|-----------|------------|--------------|
| post.id | post.name | part.id | part.name | comment.id | comment.text |
|---------|-----------|---------|-----------|------------|--------------|
| 1       | travel    | 1       | spain     | 1          | nice         |
|---------|-----------|---------|-----------|------------|--------------|
| 1       | travel    | 2       | norway    | 2          | cold         |
|---------|-----------|---------|-----------|------------|--------------|
| 1       | travel    | 3       | itaia     |            |              |
|---------|-----------|---------|-----------|------------|--------------|
| 2       | food      | 4       | cheese    | 3          | creamy       |
|---------|-----------|---------|-----------|------------|--------------|
| 2       | food      | 5       | pasta     |            |              |
|---------|-----------|---------|-----------|------------|--------------|
| 3       | sports    |         |           |            |              |
|---------|-----------|---------|-----------|------------|--------------|
| 4       | movies    | 6       | action    |            |              |
|---------|-----------|---------|-----------|------------|--------------|

我想查询的是一个列表，其中包含每个帖子名称，以及它们的评论数。所以对于下面的例子，我想要一个返回的查询:

|---------|-----------|----------------|
| post.id | post.name | nb of comments |
|---------|-----------|----------------|
| 1       | travel    | 2              |
|---------|-----------|----------------|
| 2       | food      | 1              |
|---------|-----------|----------------|
| 3       | sports    | 0              |
|---------|-----------|----------------|
| 4       | movies    | 0              |
|---------|-----------|----------------|

现在，我的查询由两部分组成，首先我们找到所有有comments 的posts，然后我们UNION使用另一个查询查找所有 posts，其中 comment.id IS NULL，但问题是有时有些 posts 有一个 part，但是没有comment，所以comment还是NULL，第二次查询还是会返回一行, 虽然它确实有至少一条评论。导致重复的 post.name 行。

我的查询:

SELECT post.id, post.name, count(*)
FROM post
JOIN part ON post.id = part.post_id
JOIN comment ON part.id = comment.part_id
GROUP BY post.name, post.part_id
UNION
SELECT post.id, post.name, NULL AS "count"
FROM post
LEFT JOIN part ON post.id = part.post_id
LEFT JOIN comment ON part.id = comment.part_id
WHERE comment.id IS NULL
GROUP BY post.name, post.part_id
ORDER BY name

这个查询会错误地返回:

|---------|-----------|----------------|
| post.id | post.name | nb of comments |
|---------|-----------|----------------|
| 1       | travel    | 2              |
|---------|-----------|----------------|
| 1       | travel    | 0              |
|---------|-----------|----------------|
| 2       | food      | 1              |
|---------|-----------|----------------|
| 2       | food      | 0              |
|---------|-----------|----------------|
| 3       | sports    | 0              |
|---------|-----------|----------------|
| 4       | movies    | 0              |
|---------|-----------|----------------|

(查看重复的旅行和食物行)

我怎样才能得到预期的结果？

Answer 1

我想你只是想要left join:

SELECT po.part_id, po.name, COUNT(c.part_id)
FROM post po LEFT JOIN
     part pa
     ON po.id = pa.post_id LEFT JOIN
     comment c
     ON pa.id = c.part_id
GROUP BY po.name, po.part_id;