sql server 使用 partition by 分区函数 解决不连续数字查询问题

本文介绍了一种在SQLServer中实现连续数字区间分段显示的方法。通过使用Partition By和Row_Number()函数组合,可以有效地处理同一标识下不连续的数字序列,并将其按连续区间展示。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

sql server表中的某一列数据为不一定连续的数字,但是需求上要求按照连续数字来分段显示,如:1,2,3,4,5,6,10,11,12,13,

会要求这样显示:1~6,10~13。下面介绍如何实现。

话不多说,直接上实例。

--创建测试表
CREATE TABLE PartitionTest
(
ID INT IDENTITY(1,1) PRIMARY KEY,
KeyID INT ,--标识ID
Num INT --号码
)
--插入数据,KeyID不同
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8000 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8001 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8002 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8003 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8004 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8005 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8006 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 1, 8007 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9000 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9001 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9002 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9003 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9004 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9005 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9006 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 2, 9007 )

--在KeyID不同的情况下,很容易就能查出这样的数据
SELECT KeyID,CONVERT(VARCHAR(10),MIN(Num))+'~'+CONVERT(VARCHAR(10),MAX(Num)) AS PartNum FROM dbo.PartitionTest WHERE KeyID IN(1,2) GROUP BY KeyID

 

 

--插入数据,KeyID相同
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1000 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1001 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1002 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1003 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1004 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1005 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1006 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 1007 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4000 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4001 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4002 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4003 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4004 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4005 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4006 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 4007 )
INSERT INTO dbo.PartitionTest(KeyID, Num ) VALUES ( 3, 5007 )

--KeyID相同的情况下,上面的查询语句无法查出需求中想要的数据
SELECT KeyID,CONVERT(VARCHAR(10),MIN(Num))+'~'+CONVERT(VARCHAR(10),MAX(Num)) AS PartNum FROM dbo.PartitionTest WHERE KeyID IN(3) GROUP BY KeyID

 

 

--利用Partition BY可以查出每一段连续数字的分组,可以利用GroupNum进行分组
SELECT
KeyID,
Num,
ROW_NUMBER() over(Partition BY KeyID ORDER BY num ) NewNum,
Num-ROW_NUMBER() over(Partition BY KeyID ORDER BY num ) GroupNum
FROM dbo.PartitionTest WHERE KeyID IN (3)

 

 

--这样写,就能达到需求的要求
SELECT t.KeyID,
CASE WHEN COUNT(NewNum)>1THEN CONVERT(VARCHAR(10),MIN(t.Num))+'~'+CONVERT(VARCHAR(10),MAX(t.Num)) ELSE CONVERT(VARCHAR(10),MIN(t.Num)) END PartNum
FROM (
SELECT
KeyID,
Num,
Num-ROW_NUMBER() over(Partition BY KeyID ORDER BY num ) NewNum
FROM dbo.PartitionTest WHERE KeyID IN (3)
) t GROUP BY t.KeyID,t.NewNum

 

 

 

 

 

以上代码可以直接拿到数据库执行进行测试噢,喜欢的点个赞吧!!!

 

转载于:https://www.cnblogs.com/nimayax/p/11586591.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值