首先来造一部分数据，表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 '',
`price` tinyint(3) NOT NULL DEFAULT '',
`status` tinyint(3) DEFAULT '',
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);
需求：每个分类下，找出两个价格最高的有效的商品。
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)
2. 每个分类找出价格最高的有效的两个商品（正确）
mysql> select a.*
-> from mygoods a
-> where (select count(*) from mygoods
-> where cat_id = a.cat_id and price > a.price and status=1 ) <2
-> and status=1
-> order by a.cat_id,a.price desc ;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
| 2 | 101 | 99 | 1 |
| 16 | 101 | 93 | 1 |
| 18 | 102 | 99 | 1 |
| 6 | 102 | 94 | 1 |
| 8 | 103 | 99 | 1 |
| 9 | 103 | 98 | 1 |
| 11 | 104 | 96 | 1 |
| 12 | 104 | 95 | 1 |
| 21 | 105 | 99 | 1 |
| 19 | 105 | 85 | 1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
3. 每个分类找出价格最高的有效的两个商品（正确）
mysql> select a.*
-> from mygoods a
-> left join mygoods b
-> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
-> where a.status=1
-> group by a.goods_id,a.cat_id,a.price
-> having count(b.goods_id) < 2
-> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
| 2 | 101 | 99 | 1 |
| 16 | 101 | 93 | 1 |
| 18 | 102 | 99 | 1 |
| 6 | 102 | 94 | 1 |
| 8 | 103 | 99 | 1 |
| 9 | 103 | 98 | 1 |
| 11 | 104 | 96 | 1 |
| 12 | 104 | 95 | 1 |
| 21 | 105 | 99 | 1 |
| 19 | 105 | 85 | 1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品（错误）
mysql> select a.*
-> from mygoods a
-> where (select count(*) from mygoods
-> where cat_id = a.cat_id and price > a.price ) <2 and status=1
-> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
| 2 | 101 | 99 | 1 |
| 18 | 102 | 99 | 1 |
| 8 | 103 | 99 | 1 |
| 9 | 103 | 98 | 1 |
| 11 | 104 | 96 | 1 |
| 12 | 104 | 95 | 1 |
| 21 | 105 | 99 | 1 |
+----------+--------+-------+--------+
7 rows in set (0.00 sec)
由上可知，如果需要增加条件的话，需要在两处增加条件。
可以将每个分组下的goods_id合并。
mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+--------+------------------------+
| cat_id | GROUP_CONCAT(goods_id) |
+--------+------------------------+
| 101 | 1,2,15,16,17 |
| 102 | 3,5,6,7,18 |
| 103 | 4,8,9,10 |
| 104 | 11,12,13 |
| 105 | 19,20,21 |
+--------+------------------------+
5 rows in set (0.00 sec)