订单操作记录表,需要获取每个订单最新的操作更新时间,以及操作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);