I have a table that has the following columns
我有一个包含以下列的表
table: route
columns: id, location, order_id
and it has values such as
它的值是
id, location, order_id
1, London, 12
2, Amsterdam, 102
3, Berlin, 90
5, Paris, 19
Is it possible to do a sql select statement in postgres that will return each row along with the id with the next highest order_id? So I want something like...
是否可以在postgres中执行sql select语句,该语句将返回每一行以及下一个最高order_id的id?所以我想要……
id, location, order_id, next_id
1, London, 12, 5
2, Amsterdam, 102, NULL
3, Berlin, 90, 2
5, Paris, 19, 3
Thanks
谢谢
3 个解决方案
#1
6
select
id,
location,
order_id,
lag(id) over (order by order_id desc) as next_id
from your_table
#2
1
Creating testbed first:
创建测试平台:
CREATE TABLE route (id int4, location varchar(20), order_id int4);
INSERT INTO route VALUES
(1,'London',12),(2,'Amsterdam',102),
(3,'Berlin',90),(5,'Paris',19);
The query:
查询:
WITH ranked AS (
SELECT id,location,order_id,rank() OVER (ORDER BY order_id)
FROM route)
SELECT b.id, b.location, b.order_id, n.id
FROM ranked b
LEFT JOIN ranked n ON b.rank+1=n.rank
ORDER BY b.id;
You can read more on the window functions in the documentation.
您可以在文档中阅读有关窗口函数的更多信息。
#3
0
yes:
是的:
select * ,
(select top 1 id from routes_table where order_id > main.order_id order by 1 desc)
from routes_table main
#1
6
select
id,
location,
order_id,
lag(id) over (order by order_id desc) as next_id
from your_table
#2
1
Creating testbed first:
创建测试平台:
CREATE TABLE route (id int4, location varchar(20), order_id int4);
INSERT INTO route VALUES
(1,'London',12),(2,'Amsterdam',102),
(3,'Berlin',90),(5,'Paris',19);
The query:
查询:
WITH ranked AS (
SELECT id,location,order_id,rank() OVER (ORDER BY order_id)
FROM route)
SELECT b.id, b.location, b.order_id, n.id
FROM ranked b
LEFT JOIN ranked n ON b.rank+1=n.rank
ORDER BY b.id;
You can read more on the window functions in the documentation.
您可以在文档中阅读有关窗口函数的更多信息。
#3
0
yes:
是的:
select * ,
(select top 1 id from routes_table where order_id > main.order_id order by 1 desc)
from routes_table main