SQL查询，计数为0计数

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部分？

6 个解决方案

#1

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

#2

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

#3

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秒以上）。

#4

You want a left join, instead of an inner join, as that allows records to not exist.

您需要左连接而不是内连接，因为它允许记录不存在。

#5

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）

#6

Use this:

用这个：

SELECT p.name,(
    SELECT COUNT(*) FROM [page-attachment] pa WHERE pa.[PAGE-ID] = p.id) as attachmentsnumber
FROM page p

#1