SQL Query, Count с 0 count

У меня есть три таблицы: страница, вложение, вложение страниц

У меня есть такие данные:

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?

Ответ 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

Ответ 2

Здесь другое решение, использующее sub-querying.

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

Ответ 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 секунд до того, как я убил внешние методы соединения и подзапроса).

Ответ 4

Вам нужно левое соединение вместо внутреннего соединения, так как это позволяет записывать записи.

Ответ 5

LEFT join - ваш друг. Чтобы узнать больше о разных типах соединений, обратитесь к http://en.wikipedia.org/wiki/Join_(SQL)

Ответ 6

Используйте это:

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