Find all numbers with the specified difference-CodePudding

I have a SQL table with rows and variable a=3. I want to find all numbers with difference >=3. At the first step, I need to take the first line, then find the next one where the difference will be > = a (3). This is 5. Then 12. Now from 12 it is necessary to find the nearest line where the difference will be >= a (3). It's 15 and so on. I want to get the final table with numbers: 1, 5, 12, 15, 20, 30

|number|
|------|
| 1    |
| 5    |
| 12   |
| 13   |
| 14   | 
| 15   |
| 20   |
| 21   |
| 30   |

CodePudding user response：

Use a self join:

select a.number
from mytable a
left join mytable b on b.number <= a.number   3
  and a.number < b.number
where b.number is null

This only selects rows from a that don't join to a number that's greater than a.number, but less than a.number 3

Slightly less code, but perhaps more obtuse:

select a.number
from mytable a
left join mytable b on b.number - a.number between 1 and 3
where b.number is null

CodePudding user response：

Exactly what i want. Thank you very much.

select distinct a.number
from mytable a
left join mytable b on b.number >= a.number - 3
  and a.number > b.number
where b.number is null or a.number-b.number>=3