sql - 将序列号转换为 SQL 中缺少值的单列
问题描述
我有一个站点的样本 ID 列表,格式如下:站点名称、样本编号,这样给定站点就有 n 个样本编号。例如,数据可能是:
site1 | 1
site1 | 2
等到任意n。
使用以下作为类似示例,以下数据将从最后一个 select 语句中得到答案:
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# 中的循环之外,还有更好的方法吗?
解决方案
这是一个依赖窗口函数的解决方案。SampleNumber
记录与其ROW_NUMBER()
在具有相同记录的组内的差异为SiteName
您提供了它所属的组。然后,外部查询聚合每个组:
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)
样本数据:
站点名称 | 样品编号 :------- | ------------: 网站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
推荐阅读
- python - 在选择另一个小部件时更改 PyQt5 中的背景
- node.js - 具有不同列的多个文件的数据结构
- github - GH 使用远程存储库时是否可以在 DOM 中显示存储库?
- excel - 在 VBA 中创建状态栏或进度栏
- neo4j - NestJS / Neo4j 仅从我的数据库的一张表中读取所有信息
- ruby-on-rails - Figma 导出的 PNG 在集成到 rails 应用程序时质量下降
- javascript - 带有 jsGrid 的 ASPNETCORE 5.0(loadData 问题)
- python - 在 Geopandas 地图上绘制六角网格
- amazon-web-services - OSError:[Errno 28] AWS EC2 实例中的设备上没有剩余空间
- python-3.x - 如何告诉 Django 模型只有一个或其他字段不能为空?