SELECT * FROM mygoods m
WHERE ( SELECTCOUNT(*) FROM mygoods s
WHERE m.cat_id = s.cat_id and s.price > m.price and s.status = 1 ) < 2and m.status = 1ORDERBY 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.priceand 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;