MySQL-选择一行-然后相对于所选行，下一行和上一行 [英] MySQL - select one row - then one next and one previous relative to the selected

问题描述

我会尽力澄清这一点.

我需要在不使用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.

这篇关于MySQL-选择一行-然后相对于所选行，下一行和上一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！