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_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
19 105 85 1
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_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
19 105 85 1
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_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
19 105 85 1
