MySQL - select one row - then one next and one pre

问题:

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.

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'

This will select one row with id 12 with the value 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.

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.

回答1:

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

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 _id into the above query, instead of 8.

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

回答2:

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

回答3:

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

回答4:

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 :)

回答5:

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
                )
           )
)