Обработка связей MYSQL не возвращает правильных результатов
Я работаю с MYSQL и пытаюсь работать в рамках возможностей MYSQL связей для ранжирования.
мой запрос:
SELECT petz.s_name,
petz.breed,
a.num,
sum(a.rank) AS rank
FROM wins_conf a
JOIN
(SELECT DISTINCT rank
FROM wins_conf
ORDER BY rank DESC LIMIT 10) b ON a.rank = b.rank
JOIN petz ON a.num=petz.num
GROUP BY petz.num
ORDER BY petz.breed,
rank DESC
который возвращает результаты:
sum(Rank)
INSANITY'S ACE OF SPADES Collie 1026 58
INSANITY'S SAVE ME Collie 1000 31
STAR GAZER'S BEAUTIFUL LIES Collie 1039 24
BANYON'S ALL IS FORGIVEN Collie 1009 19
FELIXTOWE CHERRY BLOSSOM Collie 1214 18
KE'S PRICELESS FIGUREINE Collie 1004 13
NOVABLUE'S LOVES UNENDING LEGACY Collie 1211 12
STAR GAZER'S WARRIOR OF MY HEART Collie 1059 9
INSANITY'S BE MINE Collie 1028 9
STAR GAZER'S A WILDCAT'S REVENGE Collie 1040 5
KE'S TRICKS OF THE TRADE Collie 1005 5
запись 1059 (ЗВЕЗДНЫЙ ГАЗЕР-ВОИН МОЕГО СЕРДЦА) возвращает 9 в качестве ранга, однако это должно быть 12 на основе записей в БД, которые суммируются ()
Rank
conf 33 13 1059 Best of Breed 0 0 5 0 2
conf 78 3139 1059 Best of Breed 0 0 4 0 2
conf 82 2518 1059 Best of Breed 0 0 1 0 2
conf 81 13 1059 Best in Specialty0 0 1 0 2
conf 79 13 1059 Best of Breed 0 0 1 0 2
Проведя некоторые исследования, я обнаружил, что он будет видеть только последние 3 записи в sum() столбца ранга, если 1 больше или равен 4
Любые предложения о том, как это исправить?
РЕДАКТИРОВАТЬ / ОБНОВИТЬ в ответ на AgRizzo Я только что удалил полные имена и породы для облегчения чтения, это то, что я хочу, по рангу. Я хочу отображать ранги с дубликатами, но только 10 (включая их дубликаты).
num rank
1 1026 58
2 1000 31
3 1039 24
4 1009 19
4 1214 19
5 1004 13
6 1211 12
6 1059 12
7 1028 9
8 1005 5
8 1040 5
9 1010 3
10 1276 1
Я настраиваю некоторые основные данные здесь: http://sqlfiddle.com/ В нем отсутствует часть содержимого пуха, как показано выше, но это содержание не требуется в рейтинге.
3 ответа
Попробуй это
select petz.s_name, petz.breed, a.num, sum(a.rank) as rank
from wins_conf a
JOIN petz ON a.num=petz.num
GROUP BY petz.num
ORDER BY petz.breed, rank DESC LIMIT 10
Вот вариант с рейтингом
SELECT s_name
, breed
, num
, @denserank := IF(@prevrank = rank, @denserank, @denserank + 1 ) as DenseRank
, @prevrank := rank AS rank
FROM (
SELECT petz.s_name AS s_name
, petz.breed AS breed
, a.num AS num
, sum(a.rank) as rank
FROM wins_conf a
JOIN petz
ON a.num=petz.num
WHERE petz.breed = 'Collie'
GROUP BY petz.s_name, petz.breed, a.num
ORDER BY petz.breed, rank DESC) AS temp1
JOIN (SELECT @prevscore := NULL, @denserank := 0) AS dummy
WHERE @denserank < 5
Это здесь, на SQLFiddle http://sqlfiddle.com/. Из-за ваших ограниченных данных в этом примере перечислены 5 лучших, в противном случае выбираются все записи. Измените предложение WHERE, чтобы перечислить топ-10 на вашем сайте
В других RDBMS вы, возможно, использовали бы CTE для вычисления каждого petz'общий балл (ваш SUM(rank)), а затем функцию DENSE_RANK для ранжирования их в соответствии с этими оценками.
Поскольку в MySQL отсутствуют эти удобства, мы можем использовать VIEW или подзапросы вместо CTE. DENSE_RANK может быть вычислен с переменными сеанса, как в ответе @AgRizzo, или просто как один (1) плюс количество различных оценок "лучше", чем конкретная оценка.
Я собираюсь собрать все это с помощью VIEW, а не подзапросов, потому что я думаю, что это делает логику запроса очевидной:
SET SESSION sql_mode='ANSI';
-- Compute each petz' score
CREATE OR REPLACE VIEW scorez AS
SELECT "num", SUM("rank") AS "score"
FROM wins_conf
GROUP BY 1;
-- Compute each scored petz' DENSE_RANK
CREATE OR REPLACE VIEW standingz AS
SELECT my."num",
my."score",
COUNT(DISTINCT their."score") + 1 AS "rank" -- DENSE_RANK
FROM scores my
LEFT JOIN scores their
ON their.score > my.score
GROUP BY 1, 2;
-- Now fetch the full result set
SELECT standingz.rank, petz.*
FROM petz
INNER JOIN standingz
ON petz.num = standingz.num
ORDER BY standingz.rank ASC;