How to get adjacent records?_问答_开发者_运维开发者技术经验分享

开发者 https://www.devze.com 2023-03-10 01:23 出处：网络

Not sure if this is possible (or worthwhile to do) in pure SQL, but I\'ll ask anyway. Let\'s say I have a bunch of numbered records in my database (extra spaces for clarity):

Not sure if this is possible (or worthwhile to do) in pure SQL, but I'll ask anyway.

Let's say I have a bunch of numbered records in my database (extra spaces for clarity):

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

Now given a number such as "11" how could I retrieve "10, 11, 12, 13"? i.e., all the adjacent records with no gap (all numbers must be +/- 1 from each other).

Is that开发者_运维问答 possible? If so, how?

This should do the trick. @target_id is your target value (11 in the given example).

Find the lower bound of the desired range — the largest value less than or equal to your target where value-1 doesn't exist.
Find the upper bound of the desired range — the smallest value greater than or equal to your target where value+1 doesn't exist.
return the rows such that lower-bound <= x <= upper-bound.

Easy!

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
                                 )
              )

Index your id/sequence number column and I believe performance should be pretty good.

I can't think of anything. But if read speed is important you could consider added a field that represents the 'cluster'. For 2, 3 and 4 the cluster would be 2. For 10, 11, 12 and 13 the cluster would be 10 and so on.

The downside is that you have to update the cluster whenever you update anything. The good part is that the arithmetic probably very easy.

Without knowing the context I can't comment on whether it's worthwhile or not (I'd assume not until proven otherwise,) but anything is possible!! I'll go with pseudo-code of one method first...

Create stored procedure that takes a starting row ID, which does:

Create a temporary table of ID's.

Insert a select query into that temp table from the main table matching the Row ID.

While the number of just previously executed inserts is 0:
Insert a select query joining the temp table with the main table
where the absolute value of the subtraction of the ID's is equal
to 1, and where the ID is not already in the temp table

Select the results from the temp table, joined to the main table on ID.

Given the example of 11, the first insert would insert #11, the loop would start and the second would insert #10 and #12, the third insert would only add #13, and the fourth insert would insert 0 records, ending the loop. Then you'd get a selection from the main table of ID's 10, 11, 12, 13.

If the procedure was run with a non-existent number like #14, the loop would never start and you'd get an empty result set.

Do you think you'd be able to accomplish this or should I get writing?

Assuming that the entries are all distinct and we are in a DB that supports windowing and 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=?);

I think this isn’t as fast as Nicholas’s code, but it might be worth an experiment (if your DB is capable of this query at all).