MySQL - 选择一行 - 然后是下一行和前一行相对于所选行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12430774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - select one row - then one next and one previous relative to the selected
提问by VolosBlur
I'll try to make this clear.
我会尽量说清楚。
I need to select a specific row and one row previous relative from that selected row and one row next relative from that selected row without using id's. Is this possible? Previous and next one, in short.
我需要从该选定行中选择一个特定行和前一行，从该选定行中选择下一行，而不使用 id。这可能吗？简而言之，上一个和下一个。
The reason why I can't (maybe I just don't know how) use id's, is because they are not in sequential order. They have gaps as you can see from this rather immature and random example.
我不能（也许我只是不知道如何）使用 id 的原因是因为它们不是按顺序排列的。从这个相当不成熟和随机的例子中可以看出，它们存在差距。
TABLE <-the name of the table
+----+----------------------+-------+
| id | name | value |
+----+----------------------+-------+
| 1 | some_name | asf |
+----+----------------------+-------+
| 4 | hello | A3r |
+----+----------------------+-------+
| 5 | how_do_you_do | HR5 |
+----+----------------------+-------+
| 8 | not_bad | 00D |
+----+----------------------+-------+
| 12 | i_like_women | lla |
+----+----------------------+-------+
| 13 | are_you_serious | 1Ha |
+----+----------------------+-------+
| 15 | nah_i_kid | Ad4 |
+----+----------------------+-------+
| 17 | it_is_just_the_boobs | Zc5 |
+----+----------------------+-------+
| 18 | thank_god | 102 |
+----+----------------------+-------+
| 44 | no_kidding | jjy |
+----+----------------------+-------+
First, I need to select one row based on specific value from one of its column. I know how to do that:
首先，我需要根据其中一列的特定值选择一行。我知道该怎么做：
SELECT `value`
FROM `TABLE`
WHERE name = 'i_like_women'
This will select one row with id 12 with the value lla.
这将选择 id 为 12 且值为 lla 的一行。
What I need is to select another at least two rows: one with the name 'not_bad' and one with the name 'are_you_serious' without specifying it. Or, in other words, previous and next one relative to this selected one.
我需要的是再选择至少两行：一行名称为“not_bad”，另一行名称为“are_you_serious”，但未指定。或者，换言之，相对于这个选定的上一个和下一个。
In short, three rows should be selected based on one value. I'm new to MySQL, as you can guess.
简而言之，应根据一个值选择三行。我是 MySQL 的新手，你可以猜到。
Thanks for your time and attention. Enjoy helping me.
感谢您的时间和关注。乐于帮助我。
回答by Ramiz Raja
Here is the query which will return all three records.
这是将返回所有三个记录的查询。
SELECT *
FROM `TABLE`
WHERE id >= (
SELECT id
FROM `TABLE`
WHERE id < (SELECT id FROM `TABLE` WHERE name = 'i_like_women')
ORDER BY id DESC
LIMIT 1
)
ORDER BY id ASC
LIMIT 3
回答by dash
The simplest way to do this is to exploit the fact that, although not continuous, your ids are in ascending order.
最简单的方法是利用这样一个事实，即虽然不是连续的，但您的 id 是按升序排列的。
For example:
例如：
SELECT * FROM Table WHERE id = 8
UNION
--Select the first item less than 8
SELECT * FROM Table WHERE id = (SELECT MAX(id) FROM Table WHERE id < 8)
UNION
--select the first item greater than 8
SELECT * FROM Table WHERE id = (SELECT MIN(id) FROM Table WHERE id > 8)
If you only know the string, then:
如果你只知道字符串，那么：
DECLARE _id INT
SELECT _id = id FROM Table WHERE value = 'i_like_women'
Then you can simply feed this _idinto the above query, instead of 8.
然后你可以简单地将它_id输入到上面的查询中，而不是 8。
Note you don't need to use ` to demarcate the table and column names.
请注意，您不需要使用 ` 来划分表名和列名。
回答by Tchoupi
The one before can be retrieved with:
可以通过以下方式检索前一个：
SELECT `value`
FROM `TABLE`
WHERE id < (SELECT id FROM `TABLE` WHERE name = 'i_like_women')
ORDER BY id DESC
LIMIT 1
You can do the opposite query to find the next one
你可以做相反的查询来找到下一个
回答by Vaimeo
this query is working fine on first and last record as well
此查询在第一条和最后一条记录上也能正常工作
SELECT * FROM `products` WHERE `ProductId` = (SELECT MAX(ProductId) FROM `products` WHERE ProductId < 1) AND SubCategoryId=1
UNION
SELECT * FROM `products` WHERE `ProductId` = (SELECT MIN(ProductId) FROM `products` WHERE ProductId > 1) AND SubCategoryId=1
UNION
SELECT * FROM `products` WHERE `ProductId` = (SELECT MIN(ProductId) FROM `products` WHERE ProductId < 1) AND SubCategoryId=1
UNION
SELECT * FROM `products` WHERE `ProductId` = (SELECT MAX(ProductId) FROM `products` WHERE ProductId > 1) AND SubCategoryId=1
ORDER BY ProductId ASC
i Hope this will solve your Problem :)
我希望这能解决你的问题:)
回答by Neeraj Rathod
I have found better and easy answer for the below question(finding next and previous row ),
我为以下问题找到了更好更简单的答案（找到下一行和上一行），
$neighbors = $this->WorkDescription->find('neighbors',array('field' => 'id', 'value' => 3));
it will gives output like this-
它会给出这样的输出-
Array
(
[prev] => Array
(
[WorkDescription] => Array
(
[id] => 1
[title] => Twenty
[ter_id] => 1
[cat_id] => 4
[writer] => abk
[director] => Dir
[producer] => pro
)
)
[next] => Array
(
[WorkDescription] => Array
(
[id] => 3
[title] => The Piper
[ter_id] => 1
[cat_id] => 3
[writer] => abk
[director] => Dir
[producer] => pro
)
)
)
