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

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 ,--标识IDNum 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 ) GroupNumFROM 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 PartNumFROM (SELECT KeyID,Num,Num-ROW_NUMBER() over(Partition BY KeyID ORDER BY num ) NewNumFROM dbo.PartitionTest WHERE KeyID IN (3)) t GROUP BY t.KeyID,t.NewNum

 

 

 

 

 

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

 

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