取出含有分组最大值的行

这是一个通用的问题，很多业务场景下都存在，例如取出价格最贵的那个商品，取出最新的价格、等等

 

这类问题可以归为，如何将分组中最大值所在的行取出来

 

假设我们有一张表存储着”供应商ID_商品ID_采购价格_采购时间“四列信息，其中供应商ID_商品ID为联合主键

要求：对于某个商品，取出最近一次采购的价格？

 

错误作法：

select supplier_id , product_id , last_price ,  max(last_time) 
from table 
where product_id = 12345 group by product_id

 错误原因：存在group by子句时，在select列表中出现的列只能是group by子句中提到的列，或者是使用了聚集函数的列，所以这句SQL中取到的supplier_id和last_price都可能是错的

 

 

正确作法：

select  t1.supplier_id ,  t1.product_id ,  t1.last_price , t1.last_time
from table t1
where t1. product_id = 123456 and 
            t1.last_time = ( select max(t2.last_time) from table t2 where t1.product_id = t2.product_id)

 

或者：

select  t1.supplier_id ,  t1.product_id ,  t1.last_price , t1.last_time
from table t1
left join table t2
on t1.product_id = t2.product_id and t1.last_time < t2.last_time
where t2.product_id is null;
 

 

再或者：

select  t1.supplier_id ,  t1.product_id ,  t1.last_price , t1.last_time
from table t1
join (
    select product_id , max(last_time) max_last_time
    from table
    group by product_id ) as t2
on t1.product_id = t2.product_id and t1.last_time = t2.max_last_time;