MySQL查询TOP(n)

测试表

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

1.通过子查询获取

SELECT * FROM mygoods m
    WHERE ( SELECT COUNT(*) FROM mygoods s 
        WHERE m.cat_id = s.cat_id and s.price > m.price and s.status = 1 ) < 2 
                                        and m.status = 1 ORDER BY m.cat_id,m.price DESC; 

2.通过(LEFT JOIN … ON )查询获取【推荐使用】

SELECT m.goods_id,m.cat_id,m.price,m.status FROM mygoods m 
    LEFT JOIN mygoods s ON m.cat_id = s.cat_id and m.price < s.price and s.status = 1 
        WHERE m.status = 1 GROUP BY m.cat_id,m.price 
            HAVING COUNT(m.goods_id) <2 ORDER BY m.cat_id,m.price DESC;