SQL查询，计算0计数

Question

我有三个表：page、attachment、page-attachment。

我的数据如下：

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

我希望能够获取每个页面的附件数量，即使该数字为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

我看到了这个输出：

NAME        ATTACHMENTSNUMBER
second page  2
third page   1

我想要获得这个输出：

NAME        ATTACHMENTSNUMBER
first page   0
second page  2
third page   1
fourth page  0

我该如何获取第0部分？

- Sergio del Amo

Answer 1

将你的 "内连接" 更改为 "左外连接"，这意味着 "获取所有左侧连接中的行，即使右侧没有匹配的行。"

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

- Matt Hamilton

---

正如David B.所提到的，此查询无法运行。请将group by更改为'group by page.name'。 - edosoft

Answer 2

以下是使用子查询的另一种解决方案。

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

- David B

Answer 3

根据数据库不同，为了提高速度，你可以使用UNION命令。

虽然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)
)   

我需要这个解决方案的数据库中有超过一百万个附件的20个页面。使用UNION后，运行时间仅为13秒，而在尝试外连接和子查询方法之前，我等得厌烦了，运行时间长达60秒以上。

- David M Goodwin

Answer 4

你需要使用左连接而不是内连接，因为这样可以允许记录不存在。

- Ch00k

Answer 5

LEFT JOIN 是你的好朋友。 要了解更多不同连接类型，请参考http://en.wikipedia.org/wiki/Join_(SQL)。

- aku

Answer 6

使用这个：

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

- Jpasker