首先来造一部分数据,表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)
CREATE TABLE `mygoods` (
`goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` int(11) NOT NULL DEFAULT '0',
`price` tinyint(3) NOT NULL DEFAULT '0',
`status` tinyint(3) DEFAULT '1',
PRIMARY KEY (`goods_id`),
KEY `icatid` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mygoods` VALUES (1, 101, 90, 0);
INSERT INTO `mygoods` VALUES (2, 101, 99, 1);
INSERT INTO `mygoods` VALUES (3, 102, 98, 0);
INSERT INTO `mygoods` VALUES (4, 103, 96, 0);
INSERT INTO `mygoods` VALUES (5, 102, 95, 0);
INSERT INTO `mygoods` VALUES (6, 102, 94, 1);
INSERT INTO `mygoods` VALUES (7, 102, 93, 1);
INSERT INTO `mygoods` VALUES (8, 103, 99, 1);
INSERT INTO `mygoods` VALUES (9, 103, 98, 1);
INSERT INTO `mygoods` VALUES (10, 103, 97, 1);
INSERT INTO `mygoods` VALUES (11, 104, 96, 1);
INSERT INTO `mygoods` VALUES (12, 104, 95, 1);
INSERT INTO `mygoods` VALUES (13, 104, 94, 1);
INSERT INTO `mygoods` VALUES (15, 101, 92, 1);
INSERT INTO `mygoods` VALUES (16, 101, 93, 1);
INSERT INTO `mygoods` VALUES (17, 101, 94, 0);
INSERT INTO `mygoods` VALUES (18, 102, 99, 1);
INSERT INTO `mygoods` VALUES (19, 105, 85, 1);
INSERT INTO `mygoods` VALUES (20, 105, 89, 0);
INSERT INTO `mygoods` VALUES (21, 105, 99, 1);
需求:每个分类下,找出两个价格最高的有效的商品
mysql> select a.*
-> from mygoods a
-> where (select count(*)
-> from mygoods
-> where cat_id = a.cat_id and price > a.price ) <2
-> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
| 2 | 101 | 99 | 1 |
| 17 | 101 | 94 | 0 |
| 18 | 102 | 99 | 1 |
| 3 | 102 | 98 | 0 |
| 8 | 103 | 99 | 1 |
| 9 | 103 | 98 | 1 |
| 11 | 104 | 96 | 1 |
| 12 | 104 | 95 | 1 |
| 21 | 105 | 99 | 1 |
| 20 | 105 | 89 | 0 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
标签:INSERT,mygoods,INTO,每组,99,分组,mysql,VALUES,id
From: https://www.cnblogs.com/zh718594493/p/16965521.html