mysql 分组选取第n_mysql实现先分组后排序取N个

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `test`

-- ----------------------------

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`company` varchar(255) DEFAULT NULL,

`product` varchar(255) DEFAULT NULL,

`price` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of test

-- ----------------------------

INSERT INTO `test` VALUES ('a', 'a1', '11');

INSERT INTO `test` VALUES ('a', 'a2', '22');

INSERT INTO `test` VALUES ('a', 'a3', '33');

INSERT INTO `test` VALUES ('b', 'b1', '99');

INSERT INTO `test` VALUES ('b', 'b2', '88');

INSERT INTO `test` VALUES ('b', 'b3', '77');

INSERT INTO `test` VALUES ('b', 'b4', '66');

INSERT INTO `test` VALUES ('c', 'c1', '44');

INSERT INTO `test` VALUES ('c', 'c2', '55');

INSERT INTO `test` VALUES ('c', 'c3', '66');

INSERT INTO `test` VALUES ('c', 'c4', '77');

--方式一

select * from test t1 where 3 > (select count(*) + 1  from test where company = t1.company and price > t1.price ) order by company , price desc

-- 方式二

select * from ( select *  ,(select count(*) + 1 from test where company = t1.company and price > t1.price) group_id from test t1) t where group_id < 3 order by t.company , t.price desc