SQL 查询，计数为 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query, Count with 0 count
提问by Sergio del Amo
I have three tables: page, attachment, page-attachment
我有三个表：页面，附件，页面附件
I have data like this:
我有这样的数据：
page
ID NAME
1 first page
2 second page
3 third page
4 fourth page
attachment
ID NAME
1 foo.word
2 test.xsl
3 mm.ppt
page-attachment
ID PAGE-ID ATTACHMENT-ID
1 2 1
2 2 2
3 3 3
I would like to get the number of attachments per page also when that number is 0. I have tried with:
当该数字为 0 时，我也想获得每页的附件数。我试过：
select page.name, count(page-attachment.id) as attachmentsnumber
from page
inner join page-attachment on page.id=page-id
group by page.id
I am getting this output:
我得到这个输出：
NAME ATTACHMENTSNUMBER
second page 2
third page 1
I would like to get this output:
我想得到这个输出：
NAME ATTACHMENTSNUMBER
first page 0
second page 2
third page 1
fourth page 0
How do I get the 0 part?
我如何获得 0 部分？
回答by Matt Hamilton
Change your "inner join" to a "left outer join", which means "get me all the rows on the left of the join, even if there isn't a matching row on the right."
将您的“内连接”更改为“左外连接”，这意味着“获取连接左侧的所有行，即使右侧没有匹配的行。”
select page.name, count(page-attachment.id) as attachmentsnumber
from page
left outer join page-attachment on page.id=page-id
group by page.name
回答by Amy B
Here's another solution using sub-querying.
这是使用子查询的另一种解决方案。
SELECT
p.name,
(
SELECT COUNT(*) FROM [page-attachment] pa
WHERE pa.[PAGE-ID] = p.id
) as attachmentsnumber
FROM page p
回答by David M Goodwin
Depending on the database, for speed, you could use the UNION command.
根据数据库，为了速度，您可以使用 UNION 命令。
The SQL is longer, but, depending on the database, it speeds things up by seperating "count things that are there" and "count things that are not there".
SQL 较长，但根据数据库的不同，它通过将“计算存在的事物”和“计算不存在的事物”分开来加快处理速度。
(
select page.name, count(page-attachment.id) as attachmentsnumber
from page
inner join page-attachment on page.id=page-id
group by page.id
)
UNION
(
select page.name, 0 as attachmentsnumber
from page
where page.id not in (
select page-id from page-attachment)
)
The database I need this solution for has 20 pages in more than a million attachments. The UNION made it run in 13 seconds rather than so long I got bored and tried another way (somewhere above 60 seconds before I killed the outer join and subquery methods).
我需要此解决方案的数据库有 20 页，包含超过一百万个附件。UNION 让它在 13 秒内运行，而不是太久我感到无聊并尝试了另一种方式（在我杀死外连接和子查询方法之前的某个时间超过 60 秒）。
回答by David Wengier
You want a left join, instead of an inner join, as that allows records to not exist.
您需要左联接，而不是内联接，因为它允许记录不存在。
回答by aku
LEFT join is your friend. To learn more about different join types refer to http://en.wikipedia.org/wiki/Join_(SQL)
左加入是你的朋友。要了解有关不同联接类型的更多信息，请参阅http://en.wikipedia.org/wiki/Join_(SQL)
回答by Jpasker
Use this:
用这个：
SELECT p.name,(
SELECT COUNT(*) FROM [page-attachment] pa WHERE pa.[PAGE-ID] = p.id) as attachmentsnumber
FROM page p
