如何使用SQL Server将范围值分组 [英] How to group ranged values using SQL Server

问题描述

我有一个这样的值表

978412, 400
978813, 20
978834, 50
981001, 20

如您所见，将第二个数字添加到第一个在序列中下一个之前是1。最后一个数字不在该范围内（不遵循直接顺序，如下一个值所示）。我需要的是CTE（是，理想情况下），它将输出此

As you can see the second number when added to the first is 1 number before the next in the sequence. The last number is not in the range (doesnt follow a direct sequence, as in the next value). What I need is a CTE (yes, ideally) that will output this

978412, 472
981001, 20

第一行包含范围的起始编号，然后是其中的节点总和。下一行是下一个范围，在此示例中，下一个范围与原始数据相同。

The first row contains the start number of the range then the sum of the nodes within. The next row is the next range which in this example is the same as the original data.

推荐答案

摘自Josh发表的文章，这是我的看法（经过测试并可以正常工作）：

From the article that Josh posted, here's my take (tested and working):

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

这篇关于如何使用SQL Server将范围值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！