表结构:
任务:查询每个类别下进价最高的商品
SELECT
a.`cat_id` AS 类别名,
a.goods_name AS 商品名称,
a.goods_price AS 进价
FROM
goods AS a
WHERE
(SELECT
COUNT(*)
FROM
goods AS b
WHERE b.cat_id = a.cat_id
AND b.goods_price >= a.goods_price) < 2
ORDER BY a.cat_id ASC,
a.goods_price DESC
结果:
分析:
1.定义查询,通过where来进行条件筛选。
2.使用count(*)进行计数,统计每一个a.goods_price在b.goods_price中有几个大于等于它本身的数。
3.