如何使用 SQL Server 对范围值进行分组

发布于 2024-07-12 12:17:54 字数 309 浏览 9 评论 0原文

我有一个像这样的值表，

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

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

978412, 472
981001, 20

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

原文

I have a table of values like this

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兲鉂ぱ嘚淚 2024-07-19 12:17:54

从乔什发布的文章中，这是我的看法（经过测试和工作）：

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

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

回复收藏 0 原文

り繁华旳梦境 2024-07-19 12:17:54

请查看这篇 MSDN 文章。它为您提供了问题的解决方案，它是否适合您取决于您拥有的数据量以及查询的性能要求。

编辑：

很好地使用查询中的示例，并使用他最后一个解决方案的第二种方式来获取岛屿（第一种方式导致 SQL 2005 上出现错误）。

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

我添加的内容是 (endgroup-min(start) +1) as NumNodes。这将为您提供计数。

Check out this MSDN Article. It gives you a solution to your problem, if it will work for you depends on the ammount of data you have and your performance requirements for the query.

Edit:

Well using the example in the query, and going with his last solution the second way to get islands (first way resulted in an error on SQL 2005).

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

The thing I added is (endgroup-min(start) +1) as NumNodes. This will give you the counts.

回复收藏 0 原文

~没有更多了~