mysql分组取前几条记录

测试sql语句

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、每个分类下，找出两个价格最高的商品。

SELECT
	a.* 
FROM
	mygoods a 
WHERE
	(
	SELECT
		count( * ) 
	FROM
		mygoods b 
	WHERE
		a.cat_id = b.cat_id 
		AND a.price > b.price 
	) < 2 
ORDER BY
	a.cat_id,
	a.price DESC;

2、每个分类找出价格最高的【有效的】两个商品

第一种写法

	
SELECT
	a.* 
FROM
	mygoods a 
WHERE
	(
	SELECT
		count( * ) 
	FROM
		mygoods b 
	WHERE
		a.cat_id = b.cat_id 
		AND a.price > b.price 
		AND b.STATUS = 1 
	) < 2 
	AND a.STATUS = 1 
ORDER BY
	a.cat_id,
	a.price DESC;

第二种写法

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,