一条SQL语句选取每个商店最贵的商品

[本文出自天外归云的博客园]

有两张表，一张是商店商品表shop_products

shop_en_name	product_name	price

 

一张是商店信息表shop_info

shop_en_name	shop_cn_name

 

问题：查出了每个商店最贵的商品名、商品价格以及对应所在的商店中文名

写法如下：

select
  shop_products.product_name,
  max(price) as max_price,
　shop_info.shop_cn_name
from
  shop_products
  left join shop_info on shop_products.shop_en_name = shop_info.shop_en_name
group by
  shop_products.shop_en_name
order by
  max_price desc

这里主要是对max函数以及联结查询的应用