如何选择一项，下面的一项和上面的一项在MYSQL中 [英] How to select an item, the one below and the one above in MYSQL

问题描述

我有一个ID为非整数的数据库，如下所示:

I have a database with ID's that are non-integers like this:

b01
b02
b03
d01
d02
d03
d04
s01
s02
s03
s04
s05

等字母代表产品的类型，数字代表该组中的下一个.

etc. The letters represent the type of product, the numbers the next one in that group.

我希望能够选择一个ID，例如d01，然后取回b03，d01，d02.如何在MYSQL中做到这一点?

I'd like to be able to select an ID, say d01, and get b03, d01, d02 back. How do I do this in MYSQL?

推荐答案

查找目标行，

SELECT p.id FROM product WHERE id = 'd01'

及其上方的行，两者之间没有其他行.

and the row above it with no other row between the two.

LEFT JOIN product AS p1 ON p1.id > p.id    -- gets the rows above it

LEFT JOIN       -- gets the rows between the two which needs to not exist
        product AS p1a ON p1a.id > p.id AND p1a.id < p1.id

，其下一行也类似. (左为读者的练习.)

and similarly for the row below it. (Left as an exercise for the reader.)

根据我的经验，这也是非常有效的.

In my experience this is also quite efficient.

    SELECT
        p.id, p1.id, p2.id
    FROM
        product AS p
    LEFT JOIN
        product AS p1 ON p1.id > p.id
    LEFT JOIN 
        product AS p1a ON p1a.id > p.id AND p1a.id < p1.id
    LEFT JOIN
        product AS p2 ON p2.id < p.id
    LEFT JOIN 
        product AS p2a ON p2a.id < p.id AND p2a.id > p2.id
    WHERE
        p.id = 'd01'
        AND p1a.id IS NULL
        AND p2a.ID IS NULL

这篇关于如何选择一项，下面的一项和上面的一项在MYSQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！