>想象一下,我有一个数据库,它有:
- 包含帖子名称的表
post
- 包含部分帖子的表
part
,主键字段链接到post_id(一对多( - 包含单个零件注释的表格
comment
,Primay键字段链接到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 name
及其number of comments
的列表。所以对于下面的示例,我希望一个查询返回:
|---------|-----------|----------------|
| post.id | post.name | nb of comments |
|---------|-----------|----------------|
| 1 | travel | 2 |
|---------|-----------|----------------|
| 2 | food | 1 |
|---------|-----------|----------------|
| 3 | sports | 0 |
|---------|-----------|----------------|
| 4 | movies | 0 |
|---------|-----------|----------------|
目前,我的查询由两部分组成,首先我们找到所有具有comments
的posts
,然后我们UNION
另一个查询,该查询查找comment.id IS NULL
的所有posts
,但问题是有时有些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 |
|---------|-----------|----------------|
(请参阅重复的旅行和食物行(
我该如何获得预期的结果?
我想你只需要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;