当按其他字段排序时，如何选择sql中的相邻行? [英] How can I select adjacent row in sql when ordered by a different field?

问题描述

我有一个具有以下结构的数据表:

I've got a table of data with the following structure:

id |   likes
1  |     2
2  |     5
3  |     2
4  |     6
5  |     2

如果要查找#3旁边的行，我可以使用:

If want to find the row next to #3 I can use :

SELECT * FROM table WHERE id >= 3 ORDER BY id

但是我想做的是按赞排序.当数据按喜欢排序时，看起来像这样

However what I want to do is order by table by likes. When the data is ordered by likes it looks like this

id |   likes
1  |     2
3  |     2
5  |     2
2  |     5
4  |     6

按喜欢排序时，如何选择特定ID之前或之后的行? 例如对于ID 5，我的结果将是第3行之前和第2行之后.

How can I select the rows before or after a certain id when ordered by likes? e.g. for id 5, my result would be row id 3 before and row id 2 after.

推荐答案

如果喜欢是唯一的数字，那么应该可以使用.

If likes are unique numbers, following should work.

上一个:

SELECT * FROM table WHERE likes < (SELECT likes FROM table WHERE id = ID) ORDER BY likes DESC LIMIT 1

下一个:

SELECT * FROM table WHERE likes > (SELECT likes FROM table WHERE id = ID) ORDER BY likes ASC LIMIT 1

您可以将其中1个更改为< =或> =并添加WHERE id != ID

You may change 1 of them to <= or >= and add WHERE id != ID

这篇关于当按其他字段排序时，如何选择sql中的相邻行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！