mysql 控制分组个数_MYSQL-按限制分组

有没有一种简单的方法可以将GROUP BY结果限制在前2位。以下查询返回所有结果。使用“ LIMIT 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

结果如下：

波兰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个结果

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);