Есть ли простой способ ОГРАНИЧИТЬ результаты GROUP BY лучшими 2. Следующий запрос возвращает все результаты. Используя 'ПРЕДЕЛ 2' уменьшает полный список до лучших 2 записей только.
select distinct(rating_name),
id_markets,
sum(rating_good) 'good',
sum(rating_neutral)'neutral',
sum(rating_bad) 'bad'
from ratings
where rating_year=year(curdate()) and rating_week= week(curdate(),1)
group by rating_name,id_markets
order by rating_name, sum(rating_good)
desc
Результаты в следующем:-
poland 78 48 24 12 <- keep poland 1 15 5 0 <- keep poland 23 12 6 3 poland 2 5 0 0 poland 3 0 5 0 poland 4 0 0 5 ireland 1 9 3 0 <- keep ireland 2 3 0 0 <- keep ireland 3 0 3 0 ireland 4 0 0 3 france 12 24 12 6 <- keep france 1 3 1 0 <- keep france 231 1 0 0 france 2 1 0 0 france 4 0 0 1 france 3 0 1 0
Спасибо Jon
Согласно просьбе я приложил копию структуры таблицы и некоторых данных тестирования. Моя цель состоит в том, чтобы создать единственное представление, которое имеет лучшие 2 результата каждого уникального rating_name
CREATE TABLE `zzratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_markets` int(11) DEFAULT NULL,
`id_account` int(11) DEFAULT NULL,
`id_users` int(11) DEFAULT NULL,
`dateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`rating_good` int(11) DEFAULT NULL,
`rating_neutral` int(11) DEFAULT NULL,
`rating_bad` int(11) DEFAULT NULL,
`rating_name` varchar(32) DEFAULT NULL,
`rating_year` smallint(4) DEFAULT NULL,
`rating_week` tinyint(4) DEFAULT NULL,
`cash_balance` decimal(9,6) DEFAULT NULL,
`cash_spend` decimal(9,6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `rating_year` (`rating_year`),
KEY `rating_week` (`rating_week`),
KEY `rating_name` (`rating_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2166690 DEFAULT CHARSET=latin1;
INSERT INTO `zzratings` (`id`,`id_markets`,`id_account`,`id_users`,`dateTime`,`rating_good`,`rating_neutral`,`rating_bad`,`rating_name`,`rating_year`,`rating_week`,`cash_balance`,`cash_spend`)
VALUES
(63741, 1, NULL, 100, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
(63742, 1, NULL, 101, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
(1, 2, NULL, 102, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
(63743, 3, NULL, 103, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
(63744, 4, NULL, 104, NULL, NULL, NULL, 1, 'poland', 2010, 15, NULL, NULL),
(63745, 1, NULL, 105, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
(63746, 1, NULL, 106, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
(63747, 5, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63748, 5, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63749, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63750, 3, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63751, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
(63752, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63753, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63754, 1, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63755, 1, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63756, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63757, 34, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63758, 34, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
(63759, 34, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63760, 34, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63761, 21, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63762, 21, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63763, 21, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63764, 21, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63765, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
(63766, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
(63767, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
(63768, 1, NULL, 100, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
(63769, 1, NULL, 101, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
(63770, 2, NULL, 102, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
(63771, 3, NULL, 103, NULL, NULL, 1, NULL, 'france', 2010, 15, NULL, NULL),
(63772, 4, NULL, 104, NULL, NULL, NULL, 1, 'france', 2010, 15, NULL, NULL);
Я не думаю, что в MySQL есть простой способ. Один из способов сделать это - сгенерировать номер строки для каждой строки, разделенной на группы по rating_name, а затем выбрать только строки с row_number 2 или меньше. В большинстве баз данных вы можете сделать это, используя что-то вроде:
SELECT * FROM (
SELECT
rating_name,
etc...,
ROW_NUMBER() OVER (PARTITION BY rating_name ORDER BY good) AS rn
FROM your_table
) T1
WHERE rn <= 2
К сожалению, MySQL не поддерживает синтаксис ROW_NUMBER
. Однако вы можете смоделировать ROW_NUMBER
, используя переменные:
SELECT
rating_name, id_markets, good, neutral, bad
FROM (
SELECT
*,
@rn := CASE WHEN @prev_rating_name = rating_name THEN @rn + 1 ELSE 1 END AS rn,
@prev_rating_name := rating_name
FROM (
SELECT
rating_name,
id_markets,
SUM(COALESCE(rating_good, 0)) AS good,
SUM(COALESCE(rating_neutral, 0)) AS neutral,
SUM(COALESCE(rating_bad, 0)) AS bad
FROM zzratings
WHERE rating_year = YEAR(CURDATE()) AND rating_week = WEEK(CURDATE(), 1)
GROUP BY rating_name, id_markets
) AS T1, (SELECT @prev_rating_name := '', @rn := 0) AS vars
ORDER BY rating_name, good DESC
) AS T2
WHERE rn <= 2
ORDER BY rating_name, good DESC
Результат при запуске на ваших тестовых данных:
france 1 2 0 0 france 2 1 0 0 ireland 1 4 2 0 ireland 21 3 1 0 poland 1 3 1 0 poland 2 1 0 0
Это все еще возможно с помощью одного запроса, но это немного долго, и есть некоторые предостережения, которые я объясню после запроса. Впрочем, это не столько ошибки в запросе, сколько некоторая двусмысленность в том, что означает «два верхних».
Вот запрос:
SELECT ratings.* FROM
(SELECT rating_name,
id_markets,
sum(rating_good) 'good',
sum(rating_neutral)'neutral',
sum(rating_bad) 'bad'
FROM zzratings
WHERE rating_year=year(curdate()) AND rating_week = week(curdate(),1)
GROUP BY rating_name,id_markets) AS ratings
LEFT JOIN
(SELECT rating_name,
id_markets,
sum(rating_good) 'good',
sum(rating_neutral)'neutral',
sum(rating_bad) 'bad'
FROM zzratings
WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
GROUP BY rating_name,id_markets) AS ratings2
ON ratings2.good <= ratings.good AND
ratings2.id_markets <> ratings.id_markets AND
ratings2.rating_name = ratings.rating_name
LEFT JOIN
(SELECT rating_name,
id_markets,
sum(rating_good) 'good',
sum(rating_neutral)'neutral',
sum(rating_bad) 'bad'
FROM zzratings
WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
GROUP BY rating_name,id_markets) AS ratings3
ON ratings3.good >= ratings2.good AND
ratings3.id_markets <> ratings.id_markets AND
ratings3.id_markets <> ratings2.id_markets AND
ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC
Предостережение заключается в том, что если существует более одного id_market с одинаковым "хорошим" счетчиком для одного и того же rating_name, то вы получите более двух записей. Например, если есть три ирландских id_markets с "хорошим" счетом 3, самым высоким, то как вы можете отобразить два верхних? Вы не можете. Таким образом, запрос покажет все три.
Кроме того, если бы было одно значение «3», максимальное и два счета «2», вы не смогли бы показать два верхних места, так как у вас ничья для второго места, поэтому запрос показывает все три .
Запрос будет проще, если вы сначала создадите временную таблицу с совокупным набором результатов, а затем будете работать с ней.
CREATE TEMPORARY TABLE temp_table
SELECT rating_name,
id_markets,
sum(rating_good) 'good',
sum(rating_neutral)'neutral',
sum(rating_bad) 'bad'
FROM zzratings
WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1;
SELECT ratings.*
FROM temp_table ratings
LEFT JOIN temp_table ratings2
ON ratings2.good <= ratings.good AND
ratings2.id_markets <> ratings.id_markets AND
ratings2.rating_name = ratings.rating_name
LEFT JOIN temp_table ratings3
ON ratings3.good >= ratings2.good AND
ratings3.id_markets <> ratings.id_markets AND
ratings3.id_markets <> ratings2.id_markets AND
ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC;