1.數(shù)據(jù)庫表初始化
表mygoods為商品表，cat_id為分類id域滥，goods_id為商品id纵柿，status為商品當(dāng)前的狀態(tài)位(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;
執(zhí)行結(jié)果如下所示：
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;
執(zhí)行結(jié)果如下圖所示：
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;
執(zhí)行結(jié)果如下圖所示：
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
