27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a table(a decimal(10,1),b decimal(10,1),c int )
INSERT @a SELECT 100 ,0.4,null
UNION ALL SELECT 100.5,0.4,null
UNION ALL SELECT 101,0.4,null
UNION ALL SELECT 101.5,0.8,null
UNION ALL SELECT 102,0.8,null
UNION ALL SELECT 102.5,0,NULL
UNION ALL SELECT 103,0.8,null
UNION ALL SELECT 103.5,0.8,null
UNION ALL SELECT 104,0.8,null
UNION ALL SELECT 104.5,0.8,null
UNION ALL SELECT 105,0.8,null
UNION ALL SELECT 105.5,0.8,null
UNION ALL SELECT 106,0.8,null
UNION ALL SELECT 106.5,0.8,null
UNION ALL SELECT 107,0.4,null
UNION ALL SELECT 107.5,0.8,null
UNION ALL SELECT 108,0.8,null
UNION ALL SELECT 108.5,0.8,null
UNION ALL SELECT 109,0.8,null
UNION ALL SELECT 109.5,0.8,null
UNION ALL SELECT 110,0.8,null
UNION ALL SELECT 110.5,0,null
UNION ALL SELECT 111,0,NULL
UNION ALL SELECT 111.5,0.4,null
UNION ALL SELECT 112,0.8,null
UNION ALL SELECT 112.5,0.8,null
UNION ALL SELECT 113,0.4,null
UNION ALL SELECT 113.5,0.8,null
UNION ALL SELECT 114,0.8,null
DECLARE @i int,@t decimal(10,1)
SELECT @i=0,@t=0
UPDATE @a SET c=@i,@i=CASE WHEN b<> @t THEN @i+1 ELSE @i END,@t=b
SELECT dept1=min(a),dept2=max(a),b FROM @a
GROUP BY c,b
ORDER BY 1
--result
/*dept1 dept2 b
------------ ------------ ------------
100.0 101.0 .4
101.5 102.0 .8
102.5 102.5 .0
103.0 106.5 .8
107.0 107.0 .4
107.5 110.0 .8
110.5 111.0 .0
111.5 111.5 .4
112.0 112.5 .8
113.0 113.0 .4
113.5 114.0 .8
(所影响的行数为 11 行)
*/