MySQL分组取最值

一、显式内连接

SELECT a.* from inventory_seller_product a inner JOIN 
	(select product, seller,max(total_sold) as total_sold from inventory_seller_product group by product)b 
	on a.product=b.product and a.total_sold=b.total_sold AND a.product IN ('1', '2') 
order by a.product; 

二、隐式内连接

SELECT a.* from inventory_seller_product as a 
where  total_sold = 
	(select max(total_sold) from inventory_seller_product as b where a.product = b.product ) 
and product in ('1', '2');

去重（最值相等时）

SELECT * FROM  
	(
	SELECT a.* from inventory_seller_product as a 
	where  total_sold = 
		(select max(total_sold) from inventory_seller_product as b where a.product = b.product ) 
	and product in ('1', '2')
	) c
group by product, total_sold