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
where cat_id = a.cat_id and price > a.price ) <2
order by a.cat_id,a.price desc;
2.每个分类找出价格最高的有效的两个商品
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 ;
3.每个分类找出价格最高的有效的两个商品
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;