mysql - 如何连接同一个表来检查 a>b 是否为 10

Question

表我的表

id      numbers       whereonly

1       2                1
2       35               1
3       22               1
4       20               1
5       3                1
6       70               1
7       80.15925         1
8       60               7
9       50               7

我需要按数字排序，并使用id 1进行搜索，直到找到数字行大于10的id。
期望结果:2、20、35、70、80.15925
仅当whereonly列为1时
有办法做到这一点吗？

Answer 1

您可以尝试一下:

SELECT DISTINCT t1.id AS id, t1.numbers AS numbers
FROM table AS t1
INNER JOIN table AS t2 ON t1.numbers > t2.numbers - 10
WHERE t1.whereonly = 1
GROUP BY t2.numbers
ORDER BY t1.numbers;

这是sqlfiddle .

Edit 1: As strawberry suggested

SELECT DISTINCT x.* 
  FROM mytable x
  JOIN
     ( SELECT t2.numbers t2n
            , MIN(t1.id) id
         FROM mytable t1
         JOIN mytable t2 
           ON t1.numbers > t2.numbers - 10
        GROUP 
           BY t2.numbers
     ) y
    ON y.id = x.id
    ORDER BY x.numbers
    WHERE x.whereonly = 1;

这是sqlfiddle .