mysql 排序取前4,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 '',

`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` VA