Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 978412, 400
- 978813, 20
- 978834, 50
- 981001, 20
- 978412, 472
- 981001, 20
- SELECT
- MAX(t1.gapID) as gapID,
- t2.gapID-MAX(t1.gapID)+t2.gapSize as gapSize
- -- max(t1) is the specific lower bound of t2 because of the group by.
- FROM
- ( -- t1 is the lower boundary of an island.
- SELECT gapID
- FROM gaps tbl1
- WHERE
- NOT EXISTS(
- SELECT *
- FROM gaps tbl2
- WHERE tbl1.gapID = tbl2.gapID + tbl2.gapSize + 1
- )
- ) t1
- INNER JOIN ( -- t2 is the upper boundary of an island.
- SELECT gapID, gapSize
- FROM gaps tbl1
- WHERE
- NOT EXISTS(
- SELECT * FROM gaps tbl2
- WHERE tbl2.gapID = tbl1.gapID + tbl1.gapSize + 1
- )
- ) t2 ON t1.gapID <= t2.gapID -- For all t1, we get all bigger t2 and opposite.
- GROUP BY t2.gapID, t2.gapSize
- SELECT MIN(start) AS startGroup, endGroup, (endgroup-min(start) +1) as NumNodes
- FROM (SELECT g1.gapID AS start,
- (SELECT min(g2.gapID) FROM #gaps g2
- WHERE g2.gapID >= g1.gapID and NOT EXISTS
- (SELECT * FROM #gaps g3
- WHERE g3.gapID - g2.gapID = 1)) as endGroup
- FROM #gaps g1) T1 GROUP BY endGroup
Add Comment
Please, Sign In to add comment