已知表 ID为主键,如何根据seq连续数的 个数 分别编组,写到字段GROUPID
其中
seg 字段里的整数是"分段"的连续数
如果仅仅是1时,只有1,没有2及其他的整数,记作M1,
是1到2时,记作M2,
是1到3时,记作M3,
......
......
是1到6时,记作M6
......
......
是1到999时,记作M999
提问的代码上传不了,所以写在下面的 题主的回答里
create table #t(ID int,seq int)
insert into #t
SELECT '1'ID ,'1'seq
UNION ALL SELECT '2' ,'2'
UNION ALL SELECT '3','1'
UNION ALL SELECT '4','2'
UNION ALL SELECT '5','1'
UNION ALL SELECT '6','1'
UNION ALL SELECT '7','1'
UNION ALL SELECT '8','1'
UNION ALL SELECT '9','2'
UNION ALL SELECT '10','3'
UNION ALL SELECT '11','4'
UNION ALL SELECT '12','5'
UNION ALL SELECT '13','6'
UNION ALL SELECT '14','1'
UNION ALL SELECT '15','1'
UNION ALL SELECT '16','2'
UNION ALL SELECT '17','3'
UNION ALL SELECT '18','1'
UNION ALL SELECT '19','2'
UNION ALL SELECT '20','3'
UNION ALL SELECT '21','4'
UNION ALL SELECT '22','5'
UNION ALL SELECT '23','6'
UNION ALL SELECT '24','7'
UNION ALL SELECT '25','8'
UNION ALL SELECT '26','9'
UNION ALL SELECT '27','10'
UNION ALL SELECT '28','1'
UNION ALL SELECT '29','1'
UNION ALL SELECT '30','1'
;with t as (
select id-seq as gid,min(id) minid,max(id) maxid,count(*) groupid
from #t
group by id-seq )
select a.ID,a.seq,b.groupid
from #t a
join t b on a.ID between b.minid and b.maxid
groupid前面加TM就你自己处理了
-- 已知表 ID为主键,如何根据seq连续数的 个数 分别编组,写到字段GROUPID
SELECT '1'ID ,'1'seq
UNION ALL SELECT '2' ,'2'
UNION ALL SELECT '3','1'
UNION ALL SELECT '4','2'
UNION ALL SELECT '5','1'
UNION ALL SELECT '6','1'
UNION ALL SELECT '7','1'
UNION ALL SELECT '8','1'
UNION ALL SELECT '9','2'
UNION ALL SELECT '10','3'
UNION ALL SELECT '11','4'
UNION ALL SELECT '12','5'
UNION ALL SELECT '13','6'
UNION ALL SELECT '14','1'
UNION ALL SELECT '15','1'
UNION ALL SELECT '16','2'
UNION ALL SELECT '17','3'
UNION ALL SELECT '18','1'
UNION ALL SELECT '19','2'
UNION ALL SELECT '20','3'
UNION ALL SELECT '21','4'
UNION ALL SELECT '22','5'
UNION ALL SELECT '23','6'
UNION ALL SELECT '24','7'
UNION ALL SELECT '25','8'
UNION ALL SELECT '26','9'
UNION ALL SELECT '27','10'
UNION ALL SELECT '28','1'
UNION ALL SELECT '29','1'
UNION ALL SELECT '30','1
seg 字段里的整数是"分段"的连续数
如果仅仅是1时,只有1,没有2及其他的整数,记作M1,
是1到2时,记作M2,
是1到3时,记作M3,
......
......
是1到6时,记作M6
......
......
是1到999时,记作M999
想要的结果
------------------------------------------------------------
ID seq GROUPID
1 1 TM2
2 2 TM2
3 1 TM2
4 2 TM2
5 1 TM1
6 1 TM1
7 1 TM1
8 1 TM6
9 2 TM6
10 3 TM6
11 4 TM6
12 5 TM6
13 6 TM6
14 1 TM1
15 1 TM3
16 2 TM3
17 3 TM3
18 1 TM10
19 2 TM10
20 3 TM10
21 4 TM10
22 5 TM10
23 6 TM10
24 7 TM10
25 8 TM10
26 9 TM10
27 10 TM10
28 1 TM1
29 1 TM1
30 1 TM1