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

Question

表mytable

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
Only where columnwhere Only为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。

编辑1：草莓建议

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。