SQL - 获取下一个和上一个X记录
我想在postgresql中使用一个查询,它可以根据用于某些分页的起始记录ID获取前一个和下一个X数量的记录。下一个或上一个只有1个并不难,但那不是我所需要的。SQL - 获取下一个和上一个X记录
我发现了一些接近的查询,但我对PostgreSQL查询并不了解,无法完成这些类型的复杂查询。
下面这个查询我尝试过并改变了工作,但是似乎只带回了上一个和下一个ID的标识和全部的记录。
但是我需要能够给出一个起点,例如在WHERE
子句中提供一个ID,并且如果它们存在,则只能获得10条上一条和10条下一条记录。这是可能在SQL单独做?
SELECT
m.med_id AS id,
coalesce(
LEAD(m.med_id) OVER (ORDER BY m.med_id DESC),
(select med.med_id from media as med order by med.med_id desc limit 1)
) AS nextitemid,
coalesce(
LAG(m.med_id) OVER (ORDER BY m.med_id DESC),
(select med.med_id from media as med order by med.med_id asc limit 1)
) AS previtemid
FROM media m
WHERE m.post_type = 1
ORDER BY m.med_id DESC LIMIT 20;
任何帮助将不胜感激,它可以是任何类型的SQL解决方案,可以实现这一点。
我相信所希望的输出将来自100个作为查询中提供的ID起点的50个项目的示例。
|previtemids|nextitemids|
49 51
48 52
47 53
46 54
45 55
44 56
43 57
42 58
41 59
40 60
我走近了,但一直没有能够得到它在一个查询。接近我的方案的答案比通用答案更有帮助。
with numbered as(
select your_table.*, row_number() over(order by id) as rn from your_table
)
SELECT previous.id, next.id FROM
(SELECT id, row_number() over(order by id) as inner_rn FROM numbered where rn between (select rn from numbered WHERE id = 12) - 5 and (select rn from numbered WHERE id = 12) -1) previous
INNER JOIN
(SELECT id, row_number() over(order by id) as inner_rn FROM numbered where rn between (select rn from numbered WHERE id = 12) + 1 and (select rn from numbered WHERE id = 12) + 5) next
ON previous.inner_rn = next.inner_rn
如果我正确理解你需要这个
这里12 starting point providing an ID
和5是一个和上一个X记录
(注:例如,如果没有10前一行,但有是10个下一行,并且你需要用NULL填充缺失的值,然后用FULL JOIN
代替INNER JOIN
)
所以这并没有真正产生正确的结果。我的查询接近我所需要的。只是没有按预期工作。 –
那么你需要不同的两列中的前一个和下一个xid? (如在你编辑的例子?) –
我认为这将是理想的 –
所以这就是如何完成的,我们用它来产生0-1000之间的100个随机数字..
SELECT
trunc(random()*1000) AS x
FROM generate_series(1,100);
现在我们给了80的id,我们可以在这里找到70-90的范围。
SELECT *
FROM (
SELECT rank() OVER (ORDER BY x) AS dr, x
FROM (
SELECT
trunc(random()*1000) AS x
FROM generate_series(1,100)
) AS t
) AS t
WHERE dr BETWEEN 80-10 AND 80+10;
dr | x
----+-----
70 | 702
71 | 706
72 | 718
73 | 734
74 | 751
75 | 756
76 | 774
77 | 778
78 | 805
79 | 813
80 | 829
81 | 833
82 | 839
83 | 852
84 | 853
85 | 872
86 | 884
86 | 884
88 | 892
89 | 897
90 | 905
(21 rows)
但我不会这样做。我会通过传递一个id和所需的行来进行分页。并且只允许前进。将以前的结果存储在浏览器中。
您想要列中的前10个和后10个记录,或每个记录的单独行吗? – Alex
嗯好问题@亚历克斯我会读它从PHP到数组。也许列可能更容易? –
您能否发布您想要的输出示例? – Alex