需求:查找出每个店铺销量最高的那个商品。
数据如下:
按照需求应该是取出goods_id为202,390,263的数据。
sql:
select goods_id, shop_id, title, mth_sales_count from goods_exp as a
where mth_sales_count = (SELECT max(b.mth_sales_count) from goods_exp as b where a.shop_id = b.shop_id)
GROUP BY shop_id
执行结果:
sql最后的GROUP BY shop_id主要是为了避免同个shop_id有多个mth_sales_count相等的列。
如果有额外的筛选条件可以加在字句select中。