mysql分组取每组前几条记录(排序)
表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);
先查出分组后的有效商品
SELECT a.* FROM mygoods a WHERE a.`status` = 1 GROUP BY a.cat_id, a.goods_id, a.price ORDER BY cat_id; +----------+--------+-------+--------+ | goods_id | cat_id | price | status | +----------+--------+-------+--------+ | 2 | 101 | 99 | 1 | | 15 | 101 | 92 | 1 | | 16 | 101 | 93 | 1 | | 6 | 102 | 94 | 1 | | 7 | 102 | 93 | 1 | | 18 | 102 | 99 | 1 | | 8 | 103 | 99 | 1 | | 9 | 103 | 98 | 1 | | 10 | 103 | 97 | 1 | | 11 | 104 | 96 | 1 | | 12 | 104 | 95 | 1 | | 13 | 104 | 94 | 1 | | 19 | 105 | 85 | 1 | | 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. 每个分类找出价格最高的有效的两个商品（正确）
a商品价格的最大值小于b商品价格的个数为0
SELECT a.* FROM mygoods a WHERE ( SELECT count( 1 ) FROM mygoods b WHERE a.cat_id = b.cat_id AND b.price > a.price AND b.`status` = 1 )< 2 AND a.`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. 每个分类找出价格最高的有效的两个商品（正确）
a商品价格的最大值小于b商品价格的个数为0
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.每个分类找出价格最高的有效的两个商品（错误）
SELECT a.* FROM mygoods a WHERE ( SELECT count( * ) FROM mygoods b WHERE a.cat_id = b.cat_id AND b.price > a.price )< 2 AND a.`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)
需求二：每个分类下，找出两个价格最低的有效的商品。
方法一：
SELECT a.* FROM mygoods a WHERE ( SELECT count( * ) FROM mygoods b WHERE a.cat_id = b.cat_id AND b.price < a.price AND b.`status` = 1 )< 2 AND a.`status` = 1 ORDER BY a.cat_id, a.price DESC
+----------+--------+-------+--------+ | goods_id | cat_id | price | status | +----------+--------+-------+--------+ | 16 | 101 | 93 | 1 | | 15 | 101 | 92 | 1 | | 6 | 102 | 94 | 1 | | 7 | 102 | 93 | 1 | | 9 | 103 | 98 | 1 | | 10 | 103 | 97 | 1 | | 12 | 104 | 95 | 1 | | 13 | 104 | 94 | 1 | | 21 | 105 | 99 | 1 | | 19 | 105 | 85 | 1 | +----------+--------+-------+--------+ 10 rows in set (0.00 sec)
方法二：
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 | +----------+--------+-------+--------+ | 16 | 101 | 93 | 1 | | 15 | 101 | 92 | 1 | | 6 | 102 | 94 | 1 | | 7 | 102 | 93 | 1 | | 9 | 103 | 98 | 1 | | 10 | 103 | 97 | 1 | | 12 | 104 | 95 | 1 | | 13 | 104 | 94 | 1 | | 21 | 105 | 99 | 1 | | 19 | 105 | 85 | 1 | +----------+--------+-------+--------+
需求三：每个分类下，找出价格最高的有效商品。
方法一：
SELECT a.* FROM mygoods a WHERE a.price = ( SELECT max( b.price ) FROM mygoods b WHERE a.cat_id = b.cat_id AND b.`status` = 1 ) AND a.`status` = 1 ORDER BY a.cat_id; +----------+--------+-------+--------+ | goods_id | cat_id | price | status | +----------+--------+-------+--------+ | 2 | 101 | 99 | 1 | | 18 | 102 | 99 | 1 | | 8 | 103 | 99 | 1 | | 11 | 104 | 96 | 1 | | 21 | 105 | 99 | 1 | +----------+--------+-------+--------+
5 rows in set (0.00 sec)
方法二：
SELECT a.* FROM mygoods a WHERE ( SELECT count(1) FROM mygoods b WHERE a.cat_id = b.cat_id AND a.price < b.price AND b.`status` = 1 )= 0 //<1 AND a.`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 | | 11 | 104 | 96 | 1 | | 21 | 105 | 99 | 1 | +----------+--------+-------+--------+
5 rows in set (0.00 sec)
方法三：
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 ) = 0 //<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 | | 11 | 104 | 96 | 1 | | 21 | 105 | 99 | 1 | +----------+--------+-------+--------+ 5 rows in set (0.00 sec)
具体的原理本人没搞清楚，谁有比较好的思路可以交流下
参考文章：
