mysql分组取每组前几条记录(排序)

[sql]  view plain copy 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);  

说明：

表mygoods为商品表，cat_id为分类id，goods_id为商品id，status为商品当前的状态位（1:有效，0:无效）。

需求：每个分类下，找出两个价格最高的有效的商品。

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)