请问一下,我现在在postgresql取数据,里面涉及到一个单号,对应多条数据,每条数据有时间,我想根据时间的先后来取数,比如有这几条记录:
2021 time1 txt1
2021 time2 txt2
2021 time4 txt4
2021 time3 txt3
我现在想取出
2021 time1 time4 txt1 txt4
其中time1是最早的时间,time4是最新的时间,请问想要达到这种效果,sql语法是该咋写呢?我在网上搜了没搜到合适的
2021 time1 txt1
2021 time2 txt2
2021 time4 txt4
2021 time3 txt3
我现在想取出
2021 time1 time4 txt1 txt4
其中time1是最早的时间,time4是最新的时间,请问想要达到这种效果,sql语法是该咋写呢?我在网上搜了没搜到合适的
;with t1 as (select *,row_number() over (partition by id order by time) rn from table),
t2 as (select *,row_number() over (partition by id order by time desc) rn from table)
select t1.id,t1.time as firsttime,t1.txt as firsttxt,t2.time as lasttime,t2.txt as lasttxt
from t1 join t2 on t1.id=t2.id and t1.rn=t2.rn
where t1.rn=1
如果数据量比较大的话,最好是借助临时表来处理,分别获取最早记录和最晚记录写到2个临时表,再创建索引来关联