有数据如下:
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
Uid varchar(5),
Item int,
Rn int
)
INSERT INTO #tb
VALUES('10001',10,1),
('10001',11,2),
('10001',51,3),
('10001',90,4),
('10001',95,5),
('10001',100,6),
('10002',7,1),
('10002',55,2),
('10002',58,3),
('10002',60,4),
('10002',130,5),
('10002',133,6),
('10002',200,7)
注:
上述表字段Rn为每个UID的按Item进行升序排序的序号。
想要增加一个字段Style,即最后实现的数据结果如下:
Uid Item Rn Style
10001 10 1 1
10001 11 2 1
10001 51 3 2
10001 90 4 3
10001 95 5 3
10001 100 6 3
10002 7 1 1
10002 55 2 2
10002 58 3 2
10002 60 4 2
10002 130 5 3
10002 133 6 3
10002 200 7 4
字段Style中数字的逻辑是这样的,相邻的Item数据进行比较,差值要小于或等于5。如果满足这个条件,字段Style中的序号就要给予相同的数值。这个数值随着后续数据比较中,条件的满足或不满足要进行递增。
------解决思路----------------------
;WITH CTE AS(
SELECT *,1 Style,Item Item2 FROM #tb WHERE Rn=1
UNION ALL
SELECT T1.*
,CASE WHEN T1.Item-T2.Item2<=5 THEN T2.Style
ELSE T2.Style+1
END
,T1.Item
FROM #tb T1
JOIN CTE T2 ON T1.Uid=T2.Uid AND T1.Rn=T2.Rn+1
)
SELECT Uid,Item,Rn,Style
FROM CTE ORDER BY Uid,Rn