Geek asks and answers

How to get related records?

Not sure if this is possible (or worth doing) in pure SQL, but I will ask anyway.

Let's say I have a record numbering in my database (extra spaces for clarity):

2 3 4     10 11 12 13     55 56 57  91     106 107

Now, given a number like "11", how can I get "10, 11, 12, 13"? those. all adjacent records without a gap (all numbers must be +/- 1 from each other).

Is it possible? If so, how?

+3
source share
4 answers

That should do the trick. @target_id- your target value (11 in this example).

  • Find the lower bound of the desired range; The maximum value less than or equal to your goal, where it value-1does not exist.
  • . , , value+1 .
  • , <= x <= .

!

select *
from foo t
where t.id >= ( select max(id)
                from foo x
                where x.id <= @target_id
                  and not exists ( select *
                                   from foo x1
                                   where x1.id = x.id - 1
                                 )
              )
  and t.id <= ( select min(id)
                from foo y
                where y.id >= @target_id
                  and not exists ( select *
                                   from foo y1
                                   where y1.id = y.id + 1
                                 )
              )

/ , , .

+3

. , , "". 2, 3 4 2. 10, 11, 12 13 10 ..

, . , , , .

+1

, , ( , , ), ! ...

, , :

.
, .
0:
   ,
   ID |    1 temp
temp, ID.

11, # 11, , # 10 # 12, # 13, 0 , . ID 10, 11, 12, 13.

, , 14, , .

, ?

+1

, , , windowing CTE,

WITH t1 AS
( SELECT id, id-row_number() OVER (ORDER BY id) AS discrepancy FROM t )
SELECT id FROM t1 WHERE t1.discrepancy = 
  (SELECT discrepancy FROM t1 WHERE id=?);

, isn & rsquo; t , Nicholas & rsquo; s, ( ).

0

More articles:


All Articles