mysql分组取每组前几条记录_Mysql分组取每组前几条记录

1.数据库表初始化

表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);

2.每个分类找出价格最高的两个商品

查询每个分类中价格最高的两个商品，SQL语句如下所示：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_idcat_idpricestatus

2101991

17101940

18102991

3102980

8103991

9103981

11104961

12104951

19105851

3.每个分类找出有效的价格最高的两个商品

查询每个分类中有效的价格最高的两个商品，SQL语句如下所示：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_idcat_idpricestatus

2101991

16101931

18102991

6102941

8103991

9103981

11104961

12104951

19105851

4.每个分类找出有效的价格最高的两个商品

查询每个分类中有效的价格最高的两个商品，SQL语句如下所示：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_idcat_idpricestatus

2101991

16101931

18102991

6102941

8103991

9103981

11104961

12104951

19105851      总结：由上可知如果需要增加条件的话，需要在两处增加条件。