MYSQL - 按限制分组 [英] MYSQL - Group by limit
本文介绍了MYSQL - 按限制分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否有简单的方法将GROUP BY结果限制在顶端2.以下查询返回所有结果。使用'LIMIT 2'将整个列表减少到前2名。
select distinct（rating_name），
sum（rating_good）'good'，
sum（rating_neutral）'neutral'，
sum（rating_bad）'bad'
from rating
where rating_year =年份（curdate（））和rating_week = week（curdate（），1）
by rating_name，id_markets
by order_name，sum（rating_good）
desc
结果如下： - $ /
lockquote> pre
波兰78 48 24 12 < - 保留
波兰1 15 5 0 < - 保留
波兰23 12 6 3
波兰2 5 0 0
波兰3 0 5 0
波兰4 0 0 5 $ b $爱尔兰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
感谢
Jon
根据要求，我附上了表格结构和一些测试数据的副本。我的目标是创建一个单独的视图，每个独特的评分名称前2位的结果
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，'波兰'，2010,15， NULL，NULL），
（63742,1，NULL，101，NULL，1，NULL，NULL，'poland'，2010，15，NULL，NULL），
（1，2，NULL， （NULL），NULL，1，NULL，NULL，'poland'，2010,15，NULL，NULL），
（63743,3 NULL，103，NULL，NULL，1，NULL，'poland' （63744，4，NULL，104，NULL，NULL，NULL，1，'poland'，2010，15，NULL，NULL），
（63745,1， NULL，105，NULL，1，NULL，NULL，'波兰'，2010,15，NULL，NULL），
（63746,1，NULL，106，NULL，NULL，1，NULL，'波兰'， （63747，5，NULL，100，NULL，1，NULL，NULL，'爱尔兰'，2010,15，NULL，NULL），
（63748，NULL，NULL） 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 ，NULL，NULL，NULL，1，NULL，'爱尔兰'，2010,15，NULL，NULL），
（63751,4 NULL，104，NULL，NULL，NULL，1，'爱尔兰'，2010 ，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，'爱尔兰' ，
（63755,1，NULL，101，NULL，1，NULL，NULL，'爱尔兰'，2010,15，NULL，NULL），
（63756，NULL，NULL） ，NULL，102，NULL，1，NULL，NULL，'爱尔兰'，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，'爱尔兰'，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，'爱尔兰' NULL，NULL），
（63763,21，NULL，102，NULL，1，NULL，NULL，'爱尔兰'，2010,15，NULL，NULL），
（63764,21，NULL， 2010，NULL，NULL，NULL，1，NULL，'爱尔兰'，2010,15，NULL，NULL），
（63765，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'， （63769,1，NULL，101，NULL，1，NULL，NULL，'france'，2010，15，NULL，NULL），
（63770，NULL，NULL） 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，
等...，
ROW_NUMBER（）OVER（分区BY评分名称ORDER BY好）AS
FROM your_table
）T1
WHERE rn< = 2 $不幸的是，MySQL不支持 ROW_NUMBER $ b>句法。您可以使用变量模拟 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））好，
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评级名称，id_markets
）AS T1，（SELECT @prev_rating_name：='，@rn：= 0）AS变量
ORDER BY评分名称，好的DESC
）AS T2
WHERE rn <= 2
ORDER BY评分名称，好的DESC
运行时的结果您的测试数据：
法国1 2 0 0
法国2 1 0 0
爱尔兰1 4 2 0
爱尔兰21 3 1 0
波兰1 3 1 0
波兰2 1 0 0
Is 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
这篇关于MYSQL - 按限制分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
