MySQL - 选择一行 - 然后一个下一个和一个前一个相对于所选

2022-01-24 00:00:00 select range row mysql

我会尽量说清楚.

我需要在不使用 id 的情况下从该选定行中选择一个特定行和一个相对于该选定行的前一个相对行，以及从该选定行选择一个相对于该行的下一个相对行.这可能吗?简而言之，上一个和下一个.

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.

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'

这将选择 id 为 12 且值为 lla 的一行.

This will select one row with id 12 with the value lla.

我需要另外选择至少两行:一行名为not_bad"，另一行名为are_you_serious"，但未指定.或者，换句话说，相对于这个选定的上一个和下一个.

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.

简而言之，应该基于一个值选择三行.你可以猜到，我是 MySQL 新手.

In short, three rows should be selected based on one value. I'm new to MySQL, as you can guess.

感谢您的时间和关注.乐于助人.

Thanks for your time and attention. Enjoy helping me.

推荐答案

最简单的方法是利用这样一个事实，即虽然不是连续的，但您的 id 是按升序排列的.

The simplest way to do this is to exploit the fact that, although not continuous, your ids are in ascending order.

例如:

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'

然后你可以简单地将这个 _id 输入到上面的查询中，而不是 8.

Then you can simply feed this _id into the above query, instead of 8.

请注意，您不需要使用 ` 来划分表名和列名.

Note you don't need to use ` to demarcate the table and column names.

相关文章