例如如下sql,
SELECT *
FROM ( SELECT u.*,
ROW_NUMBER() OVER(PARTITION BY p.user_id ORDER BY p.flush_time desc) RN
FROM STS_LPATROL_APPUSER_T u left JOIN STS_LPATROL_PHONEDEV_T p ON
u.user_id = p.user_id) T where RN = 1;
利用 ROW_NUMBER()OVER 函数进行分类(PARTITION BY)排序(ORDER BY ),取出多方的最新一条数据进行展示。
----------------------------------------------------------华丽的分割线---------------------------------------------------------------------
select t.id, t.name, t.address, t.create_time
from (select t1.*,
t2.create_time,
ROW_NUMBER() OVER(PARTITION BY p.user_id ORDER BY p.flush_time desc) RN
from (select * from STS_LPATROL_APPUSER_T u where u.del_tag = '0') t1
left join (select *
from STS_LPATROL_PHONEDEV_T p
where p.del_tag = '0') t2
on t1.user_id = t2.user_id) t
where RN = 1
为了阅读更清晰,1.我们将每个表的过滤条件放在一起。
2.将一方的某个字段可能在多方重复出现记录的进行分类。
3.将RN放在最外层条件,意思是去排序后的第一条。