Sélectionner le nombre de lignes d'une autre table dans une instruction SELECT Postgres

Question

Je ne sais pas comment formuler cette demande, alors aidez-moi aussi pour le titre :)

J'ai deux tables. Appelons-les A y B . El B La table a un a_id clé étrangère qui pointe vers A.id . Maintenant, je voudrais écrire un SELECT qui récupère tous les A avec une colonne supplémentaire contenant le nombre de B enregistrements par A pour chaque ligne du jeu de résultats.

J'utilise Postgresql 9 pour l'instant, mais je suppose qu'il s'agit d'une question SQL générique ?

EDITAR:

Finalement, j'ai opté pour la solution trigger-cache, où A.b_count est mis à jour par une fonction à chaque fois que B changements.

Answer 1

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

Answer 2

Je pense que le commentaire de @intgr dans une autre réponse est si précieux que je le propose comme réponse alternative car cette méthode vous permet de filtrer efficacement la colonne calculée.

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 tables, optional

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

Answer 3

La solution de sous-requête donnée ci-dessus est inefficace. La solution du trigger est probablement la meilleure dans une base de données majoritairement lue, mais pour mémoire, voici une approche de jointure qui sera plus efficace qu'une sous-requête :

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

Si vous utilisez l'ORM de Django, vous pouvez simplement écrire :

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

Answer 4

La réponse acceptée est inefficace (lente) d'après mes tests. La sous-requête de la table B s'exécute pour chaque ligne de la table A. J'utilise l'approche suivante basée sur le regroupement et la jointure. Elle fonctionne beaucoup plus rapidement :

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

Une autre variante :

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

Answer 5

Pour répondre à ma propre question :

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