MYSQL - 按限制分组
是否有一种简单的方法可以将 GROUP BY 结果限制在顶部 2.以下查询返回所有结果.使用LIMIT 2"会将整个列表减少到仅前 2 个条目.
select distinct(rating_name),id_markets，sum(rating_good) '好',sum(rating_neutral)'中性',sum(rating_bad) '坏'从收视率其中 rating_year=year(curdate()) 和 rating_week= week(curdate(),1)按 rating_name、id_markets 分组按 rating_name 排序，sum(rating_good)描述结果如下:-
波兰 78 48 24 12 <- 保持波兰 1 15 5 0 <- 保持波兰 23 12 6 3波兰 2 5 0 0波兰 3 0 5 0波兰 4 0 0 5爱尔兰 1 9 3 0 <- 保持爱尔兰 2 3 0 0 <- 保持爱尔兰 3 0 3 0爱尔兰 4 0 0 3法国 12 24 12 6 <- 保持法国 1 3 1 0 <- 保持法国 231 1 0 0法国 2 1 0 0法国 4 0 0 1法国 3 0 1 0
谢谢乔恩
<小时>根据要求，我附上了一份表格结构的副本和一些测试数据.我的目标是创建一个视图，其中包含每个唯一 rating_name 的前 2 个结果
创建表`zzratings`(`id` int(11) NOT NULL AUTO_INCREMENT,`id_markets` int(11) 默认为空，`id_account` int(11) 默认为空，`id_users` int(11) 默认为空，`dateTime` 时间戳 NULL DEFAULT CURRENT_TIMESTAMP，`rating_good` int(11) 默认为空，`rating_neutral` int(11) 默认为空，`rating_bad` int(11) 默认为空，`rating_name` varchar(32) 默认为空，`rating_year` smallint(4) 默认为空，`rating_week` tinyint(4) 默认为空，`cash_balance` 十进制(9,6)默认为空，`cash_spend`十进制(9,6)默认为空，主键(`id`)，KEY`rating_year`(`rating_year`),KEY`rating_week`(`rating_week`)，KEY `rating_name` (`rating_name`)) ENGINE=MyISAM AUTO_INCREMENT=2166690 DEFAULT CHARSET=latin1;插入`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`)价值观(63741, 1, NULL, 100, NULL, 1, NULL, NULL, '波兰', 2010, 15, NULL, NULL),(63742, 1, NULL, 101, NULL, 1, NULL, NULL, '波兰', 2010, 15, NULL, NULL),(1, 2, NULL, 102, NULL, 1, NULL, NULL, '波兰', 2010, 15, NULL, NULL),(63743, 3, NULL, 103, NULL, NULL, 1, NULL, '波兰', 2010, 15, NULL, NULL),(63744, 4, NULL, 104, NULL, NULL, NULL, 1, '波兰', 2010, 15, NULL, NULL),(63745, 1, NULL, 105, NULL, 1, NULL, NULL, '波兰', 2010, 15, NULL, NULL),(63746, 1, NULL, 106, NULL, NULL, 1, NULL, '波兰', 2010, 15, NULL, NULL),(63747, 5, NULL, 100, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63748, 5, NULL, 101, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63749, 2, NULL, 102, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63750, 3, NULL, 103, NULL, NULL, 1, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63751, 4, NULL, 104, NULL, NULL, NULL, 1, '爱尔兰', 2010, 15, NULL, NULL),(63752, 1, NULL, 105, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63753, 1, NULL, 106, NULL, NULL, 1, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63754, 1, NULL, 100, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63755, 1, NULL, 101, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63756, 2, NULL, 102, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63757, 34, NULL, 103, NULL, NULL, 1, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63758, 34, NULL, 104, NULL, NULL, NULL, 1, '爱尔兰', 2010, 15, NULL, NULL),(63759, 34, NULL, 105, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63760, 34, NULL, 106, NULL, NULL, 1, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63761, 21, NULL, 100, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63762, 21, NULL, 101, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63763, 21, NULL, 102, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63764, 21, NULL, 103, NULL, NULL, 1, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63765, 4, NULL, 104, NULL, NULL, NULL, 1, '爱尔兰', 2010, 15, NULL, NULL),(63766, 1, NULL, 105, NULL, 1, NULL, NULL, '爱尔兰', 2010, 15, NULL, NULL),(63767, 1, NULL, 106, NULL, NULL, 1, NULL, '爱尔兰', 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, '法国', 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 (选择评级名称，等等...，ROW_NUMBER() OVER (PARTITION BY rating_name ORDER BY good) AS rn从 your_table) T1哪里 rn <= 2不幸的是，MySQL 不支持 ROW_NUMBER 语法.但是，您可以使用变量模拟 ROW_NUMBER:
SELECTrating_name、id_markets、好、中性、差从 (选择*,@rn := CASE WHEN @prev_rating_name = rating_name THEN @rn + 1 ELSE 1 END AS rn,@prev_rating_name := rating_name从 (选择评级名称，id_markets，SUM(COALESCE(rating_good, 0)) 好，SUM(COALESCE(rating_neutral, 0)) 作为中性，SUM(COALESCE(rating_bad, 0)) AS 不好来自 zzratingsWHERE 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 varsORDER BY rating_name，良好的DESC) 作为 T2哪里 rn <= 2ORDER BY rating_name，良好的DESC在测试数据上运行的结果:
<前>法国 1 2 0 0法国 2 1 0 0爱尔兰 1 4 2 0爱尔兰 21 3 1 0波兰 1 3 1 0波兰 2 1 0 0Is there a simple way to LIMIT the GROUP BY results to the top 2. The following query returns all the results. Using 'LIMIT 2' reduces the overall list to the top 2 entries only.
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
Results in the following :-
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
Thanks Jon
As requested I have attached a copy of the table structure and some test data. My goal is to create a single view that has the top 2 results from each unique 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);
解决方案
I don't think that there is a simple way in MySQL. One way to do this is by generating a row number for each row partitioned in groups by rating_name, and then only select the rows with row_number 2 or less. In most databases you could do this using something like:
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
Unfortunately, MySQL doesn't support the ROW_NUMBER syntax. You can however simulate ROW_NUMBER using variables:
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
Result when run on your test data:
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
相关文章