SQL分组求最大值

订单操作记录表，需要获取每个订单最新的操作更新时间，以及操作ID。使用 over 以及 row_number() 来实现

SELECT * from(
SELECT OPERATIONID,ORDERNO,UPDATETIME,row_number() over(partition by orderno ORDER BY updatetime desc)rn
from OPERATIONRECORD
)t where t.rn <=1;

若不可使用over函数，则使用not exist

select o.* from OPERATIONRECORD o
where not EXISTS 
(SELECT 1 from OPERATIONRECORD where ORDERNO=o.ORDERNO and(UPDATETIME>o.UPDATETIME))
order by OPERATIONID desc;

2017.12.14

使用case when 根据列值添加查询条件

select a.id, a.name, a.age from A a left join B b on a.id = b.id left join C c on a.name = c.name where 
a.team = (case when a.age is not null then b.team else a.team end);