在 SQL 中将序列号转换为具有缺失值的单列

Question

>我有一个网站的示例 ID 列表，格式为： 站点名称、样本编号，以便给定站点有 n 个样本编号。例如，数据可以是：

site1 | 1
site1 | 2

等到任意 n。

使用下面的类似示例，下面的数据将从最后一个选择语句中得到答案：

CREATE TABLE #SiteWithId(SiteId VARCHAR(50), SampleNumber INT)
INSERT INTO #SiteWithId
(
SiteId,
SampleNumber
)
values 
(   'test', -- SiteId - varchar(50)
1  -- SampleNumber - int
),
('test',2),
('test',3),
('test',4),
('test',6),
('test',7)
SELECT * FROM #SiteWithId
DROP TABLE #SiteWithId
--the answer
SELECT 'test', '1-4,6-7'

请注意，缺少的项目会在最终答案中造成中断。

我知道我可以在 C# 中遍历数据集并创建这样的项目。但是有谁知道只使用 sql 创建这样的值，这样我就可以吐出报告所需的值？我想我也可以在 sql 中做一个循环，但我担心它会不可扩展，因为这不是 sql 真正要做的事情。

除了 sql 或 c# 中的循环之外，还有更好的方法来做到这一点吗？

Answer 1

这是一个依赖于窗口函数的解决方案。记录的SampleNumber与其在具有相同SiteName的记录组中的ROW_NUMBER()之间的差异为您提供了它所属的组。然后，外部查询聚合每个组：

SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
FROM (
SELECT 
SiteName, 
SampleNumber, 
ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
FROM mytable
) x
GROUP BY SiteName, (SampleNumber - rn)

DB小提琴上的演示：

示例数据：

网站名称 |样本编号 :------- |-----------: 站点 1 |           1 站点 1 |           2 站点 1 |           3 站点 1 |           5 站点 1 |           6 站点 1 |           8 站点 1 |           9 站点 1 |          10

结果：

网站名称 |采样范围 :------- |:---------- 站点 1 |1-3        站点 1 |5-6        站点 1 |8-10

---

如果希望将每个站点的所有范围连接在一个记录中，可以添加另一个聚合级别并使用STRING_AGG()(自 SQL Server 2017 起可用)：

SELECT SiteName, STRING_AGG(SampleRange,',') SampleRange
FROM (
SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
FROM (
SELECT 
SiteName, 
SampleNumber, 
ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
FROM mytable
) x
GROUP BY SiteName, (SampleNumber - rn)
) y
GROUP BY SiteName

演示：

网站名称 |采样范围 :------- |:----------- 站点 1 |1-3,5-6,8-10