Проблемы с присоединением к MySQL
Следующий запрос возвращает мне странные результаты:
SELECT
`Statistics`.`StatisticID`,
COUNT(`Votes`.`StatisticID`) AS `Score`,
COUNT(`Views`.`StatisticID`) AS `Views`,
COUNT(`Comments`.`StatisticID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10
Я запрашиваю это в такой структуре таблицы:
(только данные, относящиеся к Statistics.StatisticID = 8)
Голоса
StatisticID
8
Взгляды
StatisticID
8
8
Комментарии
StatisticID
8
8
8
8
8
Теперь, если я выполню этот запрос, я получу следующий набор результатов:
StatisticID Score Views Comments
8 5 5 5
Я знаю, откуда берется 5 - количество комментариев - и это сработает, если я уберу заявление о комментариях. Может ли кто-нибудь отладить это, поскольку это вне моей досягаемости (я относительно новичок в SQL).
Спасибо, Росс
Ответы 2
При таком соединении вы будете дублировать данные столько раз, сколько математических строк вы найдете в других таблицах. Это нормально, если у вас есть только одна соответствующая строка в каждой таблице.
Запустите этот запрос без группировки, и вы поймете, почему вы получаете одинаковый результат по всем пунктам. Однако я бы предположил, что вы получите 10 в счетчике для каждого поля (1 * 2 * 5) Если вы хотите решить эту проблему, вам нужно вызвать подзапрос для каждого подсчета.
SELECT
s.`StatisticID`,
(SELECT COUNT(*) FROM Votes WHERE Votes.StatisticID = s.StatisticID) AS Score,
(SELECT COUNT(*) FROM Views WHERE Views.StatisticID = s.StatisticID) AS Views,
(SELECT COUNT(*) FROM Comments WHERE Comments.StatisticID = s.StatisticID) AS Comments,
FROM `Statistics` s
LIMIT 0, 10
Есть определенные проблемы с производительностью, если конечный результат большой. Вы можете немного оптимизировать его, присоединившись к одной из таблиц, однако я не уверен, будет ли анализатор запросов достаточно умен, чтобы запускать только один раз для каждого сгруппированного элемента. Надеюсь, так и будет. В противном случае вы могли бы разбить его на разные запросы.
Я бы не стал беспокоиться о проблемах с производительностью - это решение подзапроса будет работать отлично и не будет выполнять никаких ложных действий!
Я больше думал, что если внешний результат будет иметь 1000 строк, тогда потребуется также выполнить 3000 подзапросов. В этом случае, вероятно, будет быстрее присоединиться к одной таблице за раз и вызвать запрос 3 раза.
договорились о подзапросе - если много внешних результатов. Но на данный момент, я полагаю, у нас есть предел 0, 10. Кроме того, вы дважды выбирали из голосов, третий suybquery должен быть из комментариев.
Хм, я это изменил, каким-то образом моя правка была отменена, или это был сбой в обновлении.
Спасибо, я бы не знал о проблемах с перфомансом, если бы вы мне на это не указали :)
Предполагая, что у вас есть поле id или подобное для голосов / просмотров / комментариев:
SELECT
`Statistics`.`StatisticID`,
COUNT(DISTINCT `Votes`.`VoteID`) AS `Score`,
COUNT(DISTINCT `Views`.`ViewID`) AS `Views`,
COUNT(DISTINCT `Comments`.`CommentID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10
Не тестировал, но думаю, должно работать. (Мы должны использовать другое поле, потому что statisticID всегда будет одинаковым в данной группе ...)
Собственно, это лучшее решение. Не знал, что вы можете использовать DISTINCT в счетчике, просто протестировал его, и он работает.
Росс, не могли бы вы лучше рассмотреть свои столы? Я не совсем уверен, что я смотрю - это ERD (en.wikipedia.org/wiki/Entity-relationship_model) или то, что вы храните в таблицах.