选择邻居表项

Question

我有一个带有文本注释的表。我的应用程序显示按pinned_flag排序的条目，然后按note_date排序:

id | note_text | note_date | pinned_flag
----------------------------------------
1  | note1     | 01/04/2021 |  1 
----------------------------------------
3  | note2     | 01/02/2021 |  1 
----------------------------------------
5  | note3     | 01/05/2021 |  0 
----------------------------------------
4  | note4     | 01/03/2021 |  0 
----------------------------------------
2  | note5     | 01/01/2021 |  0
----------------------------------------

我需要选择下面的1条记录和上面的1条记录与给定id的记录。(对于条目5，结果将是3和4)我可以使用哪些查询来做到这一点?

Answer 1

如果您的SQLite版本是3.25.0+，您可以使用ROW_NUMBER()窗口函数:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY pinned_flag DESC, note_date DESC) rn 
FROM tablename
)
SELECT id, note_text, note_date, pinned_flag
FROM cte
WHERE (SELECT rn FROM cte WHERE id = 5) IN (rn - 1, rn + 1); 

如果你不能使用窗口函数，但你的SQLite版本是3.15.0+，你可以使用UNION ALL和行值:

WITH cte AS (SELECT * FROM tablename WHERE id = 5)
SELECT * FROM (
SELECT * FROM tablename
WHERE (pinned_flag, note_date) > (SELECT pinned_flag, note_date FROM cte)
ORDER BY pinned_flag, note_date LIMIT 1
)
UNION ALL
SELECT * FROM (
SELECT * FROM tablename
WHERE (pinned_flag, note_date) < (SELECT pinned_flag, note_date FROM cte)
ORDER BY pinned_flag DESC, note_date DESC LIMIT 1
);

对于以前的版本:

WITH cte AS (SELECT * FROM tablename WHERE id = 5)
SELECT * FROM (
SELECT * FROM tablename
WHERE pinned_flag > (SELECT pinned_flag FROM cte)
OR (pinned_flag = (SELECT pinned_flag FROM cte) AND note_date > (SELECT note_date FROM cte))
ORDER BY pinned_flag, note_date LIMIT 1
)
UNION ALL
SELECT * FROM (
SELECT * FROM tablename
WHERE pinned_flag < (SELECT pinned_flag FROM cte)
OR (pinned_flag = (SELECT pinned_flag FROM cte) AND note_date < (SELECT note_date FROM cte))
ORDER BY pinned_flag DESC, note_date DESC LIMIT 1
);