[SQL] Подсчёт записей в дочерних таблицах.

Flack_bfsp

Представим себе такую ситуацию. Есить таблица Хозяева, состоящая из полей id и name.
Есть таблица Рабы, состоящая из полей id, master_id и name, где master_id - внешний ключ, связанный с полем id таблицы Хозяева. Чтобы вывести таблицу хозяев, в которой указывалось бы, сколько у каждого рабов, делаем что-то в этом роде
SELECT Хозяева.id, Хозяева.name, count(Рабы.id) AS slaves FROM Хозяева LEFT JOIN Рабы ON Хозяева.id = master_id GROUP BY Хозяева.id;
Теперь представим, что у хозяев бывает несколько видов рабов, занесённых в разные таблицы, в каждой из которых есть поле master_id. Как эффективно посчитать для каждого хозяина число каждого вида из рабов? У меня пока две идеи - либо подсчитывать каждый count по очереди, и полученный селект присоединять слева к новой таблице рабов, либо объединять таблицы рабов UNION-ом, а потом делать count по разным столбцам.
Какие ещё будут идеи?

gopnik1994

опять без подзапросов что ли?

6yrop

SELECT Хозяева.id, Хозяева.name, count(Рабы.id) AS slaves FROM Хозяева LEFT JOIN Рабы ON Хозяева.id = master_id GROUP BY Хозяева.id
...
подсчитывать каждый count по очереди, и полученный селект присоединять слева к новой таблице рабов

Можно сначала делать GROUP BY для каждого типа рабов, а потом все это JOIN-ить.
Твой запрос оптимизатор скорее всего перезапишет, и планы у них будут одинаковые. Попробуй. Хотя с левым join-ом может и не перезапишет....

Flack_bfsp

Ну вот сейчас я это делаю так:
SELECT m_id, m_name, slaves1, count(Рабы2.id) AS slaves2 FROM
(SELECT Хозяева.id as m_id, Хозяева.name as m_name, count(Рабы1.id) AS slaves1 FROM Хозяева LEFT JOIN Рабы1 ON m_id = master_id GROUP BY m_id) as table1
LEFT JOIN Рабы2 ON m_id = master_id GROUP BY m_id
и аналогично для трёх и более связанных таблиц. Но я сомневаюсь, что это эффективно, да и выглядит как-то не очень.

6yrop

я говорил про вот такое



SELECT

	Хозяева.*,

	ISNULL(T1.slaves1, 0

	ISNULL(T2.slaves2, 0)

FROM

	Хозяева

LEFT OUTER JOIN

	(SELECT master_id, COUNT(*) AS slaves1

	FROM Рабы1

	GROUP BY master_id) T1

ON T1.master_id = Хозяева.id

LEFT OUTER JOIN

	(SELECT master_id, COUNT(*) AS slaves2

	FROM Рабы2

	GROUP BY master_id) T2

ON T2.master_id = Хозяева.id

Сравни планы.

Flack_bfsp

Не, ну это, имхо, ещё медленнее. У меня два селекта, у тебя - три.

timefim

В если все таблицы сfulljoinить и получившийся результат сгруппировать?

Flack_bfsp

А как ты предлагаешь группировать?
Я пробовал лефтджойнить их все и потом группировать по номеру хозяина - тогда для каждого хозяина все количества рабов перемножаются.

6yrop

Не, ну это, имхо, ещё медленнее. У меня два селекта, у тебя - три.

Время выполнения запроса не зависит от количества селектов.
Мои эксперименты на MSSQL2005 показали, что при большом количестве записей в обоих таблицах планы твоего и моего запросов полностью совпадают, соответственно время выполнение тоже одинаковое. На небольших объемах мой запрос чаще выигрывает

6yrop

кстати, ты это на MySQL что ли делаешь

? В нормальных базах slaves1 в селекте нельзя указывать, поскольку он не входит в GROUP BY.

Flack_bfsp

Ээээ... Ну вообще да, мускул, но я хочу, чтобы запросы были переносимые.

6yrop

тогда в селекте можно указывать либо поля, которые указаны в GROUP BY, либо агригирующие функции.

Flack_bfsp

Да, вижу. Это расширение мускула.
Хорошо, а зачем нужны ISNULL в том селекте, который ты написал?

Flack_bfsp

А, понял. Только это в мускуле не работает. Да мне и не очень важно, нулл вернул каунт или не нулл - я это внешней программой обрабатываю как ноль. А если ISNULL убрать, то всё работает. Спасибо, так и переделаю запросы.

Оставить комментарий