Избегайте указания каждого поля таблицы в предложении GROUP BY при использовании Left Join

У меня есть простая база данных SQL Server 2008 с двумя таблицами:

TableA: 
 (PK)"ID"
 "Field"

и

TableB:
 (PK)"ID"
 (FK)"ID_TableA"
 "Field"

Я хочу выделить все поля в TableA, а также количество соответствующих строк в TableB для каждой строки TableA:

SELECT A.*, 
 COUNT(B."ID") as "B number"
FROM "TableA" A
LEFT JOIN "TableB" B ON (A."ID" = B."ID_TableA")
GROUP BY A."ID", A."Field"

Это хорошо работает, но у меня есть эта проблема: если TableA дополнительно модифицируется (скажем, нам нужно добавить еще один столбец Field2), я должен обновить приведенный выше оператор SELECT, чтобы включить это поле в GROUP BY. В противном случае я получаю эту ошибку при выполнении операции:

"Столбец" TableA.Field2 "недопустим в списке выбора, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY"

Есть ли способ избежать этого, поэтому я могу изменить свой TableA, не обновляя все утверждения, подобные приведенному выше?

3 ответа

Вы можете использовать это (сначала Group By в таблице B, затем присоединиться к таблице A):

SELECT A.*, 
 COALESCE("B number", 0) AS "B number"
FROM "TableA" A
 LEFT JOIN 
 ( SELECT B."ID_TableA", COUNT(B."ID") as "B number"
 FROM "TableB" B 
 GROUP BY B."ID_TableA"
 ) AS B ON (A."ID" = B."ID_TableA")


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

SELECT A.*
 , (SELECT COUNT(*) FROM "TableB" WHERE "ID_TableA" = A."ID") AS "B number"
FROM "TableA" A

Обычно db-движок оптимизирует их, чтобы соответствовать (или бить) производительность соединения.


Во-первых, это плохая идея использовать *, чтобы новые столбцы не прерывали работу. Но я думаю, вы могли бы сделать это и получить нужный результат без указания столбцов A:

SELECT
 A.*, 
 COUNT(B."ID") OVER (PARTITION BY B."ID_TableA") as "B number"
FROM "TableA" A
LEFT JOIN "TableB" B ON (A."ID" = B."ID_TableA")

licensed under cc by-sa 3.0 with attribution.