SUM Union All как подзапрос в инструкции select
У меня есть три таблицы: quiz, comment, question.
Все они имеют внешний ключ к activity с именем activityID.
Я хочу получить sum количество записей в каждой таблице в одном запросе, возвращая для всех действий соответствующий sum.
Вот моя попытка:
SELECT
`activity`.`id` AS `activityID`,
`activity`.`name`,
(SELECT SUM(`count`) FROM (
SELECT COUNT(*) AS `count`
FROM `quiz` WHERE `quiz`.`activityID` = `activity`.`id`
UNION ALL
SELECT COUNT(*) AS `count`
FROM `comment` WHERE `comment`.`activityID` = `activity`.`id`
UNION ALL
SELECT COUNT(*) AS `count`
FROM `question` WHERE `question`.`activityID` = `activity`.`id`
) `union`) AS `interactions`
FROM
`activity`
WHERE 1
AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC
Это не работает, потому что activity.id не найден во вложенном подзапросе. Можно ли добавить это значение в область вложенного подзапроса? Или, что еще лучше, есть более простой способ сделать это без запуска трех отдельных подзапросов и их добавления?
Это лучшее решение, которое у меня есть (без использования UNION):
SELECT
`activity`.`id` AS `activityID`,
`activity`.`name`,
( SELECT COUNT(*) AS `count`
FROM `quiz` WHERE `quiz`.`activityID` = `activity`.`id` ) +
( SELECT COUNT(*) AS `count`
FROM `comment` WHERE `comment`.`activityID` = `activity`.`id` ) +
( SELECT COUNT(*) AS `count`
FROM `question` WHERE `question`.`activityID` = `activity`.`id` ) AS `interactions`
FROM
`activity`
WHERE 1
AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC
Спасибо.
В чем причина WHERE 1 AND ...?
Просто для лучшей читаемости запроса. Образца нет, так как запрос не работает. Он должен просто вернуть три столбца, один с идентификатором действия, другой с именем действия и, наконец, суммой всего набора объединений.
Ответы 1
Вы можете присоединиться ко всем столам и получить счет.
SELECT
`activity`.`id` AS `activityID`,
`activity`.`name`,
SUM(I.count) as interactions
FROM
`activity` A INNER JOIN
(SELECT `quiz`.`activityID`, COUNT(*) AS `count`
FROM `quiz`
GROUP BY `quiz`.`activityID`
UNION ALL
SELECT `comment`.`activityID`, COUNT(*) AS `count`
FROM `comment`
GROUP BY `comment`.`activityID`
UNION ALL
SELECT `question`.`activityID`, COUNT(*) AS `count`
FROM `question`
GROUP BY `question`.`activityID`
) interactioncounts I on A.id = I.activityID
WHERE 1
AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC
Мне нужно использовать union, как указано в вопросе - есть огромный недостаток производительности при использовании inner join для этой конкретной задачи.
Вы не сказали, что в вопросе есть недостаток внутреннего соединения, и просили более простое решение, поэтому я опубликовал это. Вы можете переместить свой подзапрос с помощью union из части select в часть from, чтобы иметь возможность использовать идентификатор действия. Я могу обновить ответ примерно через час.
@ MaurícioGiordano Изменил запрос, чтобы использовать union, как и у вас. Недостатком здесь является то, что он вычисляет итоги для всех идентификаторов активности. Сообщите мне, если это вызывает серьезную озабоченность. Я пытаюсь найти решение, позволяющее избежать этого.
Я понял, что для этой конкретной проблемы самый быстрый способ - добавить несколько подзапросов в оператор SELECT, отфильтрованный предложением WHERE ??.activityID = activity.id. Поскольку в каждой таблице у меня миллионы записей, UNION ALL без предложения WHERE - не самый разумный выбор.
Я отредактировал свой вопрос, выбрав лучшее решение, которое у меня есть. Спасибо за вашу помощь!
пожалуйста, разместите образцы данных и желаемые результаты. Спасибо