Выберите количество строк в другой таблице в инструкции Postgres SELECT

Я не знаю, как это сделать, поэтому, пожалуйста, помогите мне с этим названием.:)

У меня две таблицы. Назовите их A и B. Таблица B имеет внешний ключ a_id, который указывает на A.id. Теперь я хотел бы написать инструкцию SELECT, которая извлекает все записи A, с дополнительным столбцом, содержащим счетчик B записей в строке A для каждой строки в наборе результатов.

Я использую Postgresql 9 прямо сейчас, но я предполагаю, что это будет общий вопрос SQL?

EDIT:

В итоге я пошел на решение триггер-кеш, где A.b_count обновляется через функцию каждый раз, когда изменяется B.

Ответ 1

SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A

Ответ 2

Я думаю, что комментарий @intgr в другом ответе настолько ценен, что я предлагаю это альтернативный ответ, так как этот метод позволяет эффективно фильтровать расчетный столбец.

SELECT
  a.*
  COUNT(b.id) AS b_count

FROM a
INNER JOIN b on b.a_id = a.id
WHERE a.id > 50 AND b.ID < 100 -- example of filtering joined tabled

GROUP BY a.id
HAVING COUNT(b.id) > 10 -- example of filtering calculated column
ORDER BY a.id

Ответ 3

Решение подзапроса, приведенное выше, неэффективно. Решение триггера, вероятно, лучше всего в базе данных с большей информацией, но для записи здесь используется подход соединения, который будет работать лучше, чем подзапрос:

SELECT a.id, a.xxx, count(*)
FROM a JOIN b ON (b.a_id = a.id)
GROUP BY a.id, a.xxx

Если вы используете Django ORM, вы можете просто написать:

res = A.objects.annotate(Count('b'))
print res[0].b__count  # holds the result count

Ответ 4

Принятый ответ неэффективен (медленный) на основе моих тестов. Подзапрос таблицы B, выполняемый для каждой строки таблицы A. Я использую следующий подход, основанный на группировке и объединении. Он работает намного быстрее:

SELECT A.id, QTY.quantity FROM A
LEFT JOIN
    (SELECT COUNT(B.a_id) AS quantity, B.a_id FROM B GROUP BY B.a_id) AS QTY
ON A.id = QTY.a_id

Другой вариант:

SELECT A.id, COUNT(B.a_id) AS quantity FROM A
LEFT JOIN B ON B.a_id = A.id
GROUP BY A.id

Ответ 5

Чтобы ответить на мой собственный вопрос:

SELECT a.id, a.other_column, ..., 
(SELECT COUNT(*) FROM b where b.a_id = a.id) AS b_count
FROM a;