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 ,--标识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