以IN子句的顺序查询数据 [英] query data in the order of IN clause
本文介绍了以IN子句的顺序查询数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
select osmid,ST_X(shape),ST_Y(shape)
from osmpoints
where (osmpoints.osmid, osmpoints.osmtimestamp)
IN (select osmid,MAX(osmtimestamp)
from osmPoints
GROUP BY osmid
Having MAX(osmtimestamp) <= '2019-09-16T01:23:55Z'
AND osmid in ('4426786454','1861591896','1861591869','1861591895',
'4426786455','2038185115','1861591853','6797739995',
'2299605892','6797739994','1861591898','2038185111','4426786454'));
当我运行此查询时,我得到基于osmid列的排序行.但是我的问题是如何按IN子句中使用的osmid顺序获取行?
when I run this query, I get sorted rows based on osmid column. but my question is how can I get rows in order of osmid that use in the IN clause?
推荐答案
您可以使用与此处显示的相同技术:
with input(idlist) as (
values (
array['4426786454','1861591896','1861591869','1861591895',
'4426786455','2038185115','1861591853','6797739995',
'2299605892','6797739994','1861591898','2038185111','4426786454']::text[])
)
select p.osmid,ST_X(shape),ST_Y(shape)
from osmpoints p
cross join input
where (p.osmid, p.osmtimestamp) IN (select osmid,MAX(osmtimestamp)
from osmPoints
cross join input
GROUP BY osmid
Having MAX(osmtimestamp) <= '2019-09-16T01:23:55Z'
AND osmid = any(idlist))
order by array_position(idlist, osmid);
这篇关于以IN子句的顺序查询数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文