with cte as (select 99055 as id,1 gno, 7 ono union all select 99055 as id,2 gno, 13 ono union all select 99055 as id,4 gno, 34 ono union all select 99055 as id,4 gno, 43 ono union all select 99055 as id,4 gno, 67 ono union all select 99055 as id,8 gno, 70 ono union all select 99055 as id,7 gno, 82 ono union all select 99055 as id,9 gno, 94 ono union all select 99055 as id,4 gno, 97 ono union all select 104998 as id,1 gno, 1 ono union all select 104998 as id,2 gno, 2 ono union all select 104998 as id,4 gno, 3 ono union all select 104998 as id,4 gno, 4 ono union all select 104998 as id,10 gno, 5 ono union all select 104998 as id,4 gno, 6 ono ), cte1 as (select *,ROW_NUMBER()over(partition by id order by ono) as n from cte), cte2 as (select * ,1 as groupid from cte1 where n=1 union all select a.*,case when a.gno=b.gno then b.groupid else b.groupid+1 end as groupid from cte1 as a join cte2 as b on a.n=b.n+1 and a.id=b.id ) select * from cte2 order by id,ono--结果id gno ono n groupid ----------- ----------- ----------- -------------------- ----------- 99055 1 7 1 1 99055 2 13 2 2 99055 4 34 3 3 99055 4 43 4 3 99055 4 67 5 3 99055 8 70 6 4 99055 7 82 7 5 99055 9 94 8 6 99055 4 97 9 7 104998 1 1 1 1 104998 2 2 2 2 104998 4 3 3 3 104998 4 4 4 3 104998 10 5 5 4 104998 4 6 6 5(15 行受影响)
dense_rank() over (partition by bomid order by 工艺代码) as groupid
大版主 这个不行吧。DENSE_RANK 会把4 都分在一组吧？
嗯，看错数据，最后的几行看来不是这么分的，采用with cte如下： ;with acherat as ()
;with acherat as ( select *,rid=row_number() over (partition by bomid order by 排序字段) from a )select *,groupid = dense_rank() over (partition by bomid order by 排序字段 - rid) from acherat
;with acherat as ( select *,rid=row_number() over (partition by bomid order by 排序字段), lid=row_number() over (partition by bomid,工艺代码 order by 排序字段) from cte1 )
select *,groupid = dense_rank() over (partition by bomid order by lid - rid) from acherat
不用递归也可以吧~~ WITH CTE AS( SELECT ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN ,ROW_NUMBER()OVER(PARTITION BY [工艺代码] ORDER BY BOMID,[排序字段])RN2 ,BOMID,[工艺代码],[排序字段],ID FROM 表A ) ,CTE2 AS( SELECT ROW_NUMBER()OVER(PARTITION BY BOMID,[工艺代码],RN-RN2 ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],ID,RN-RN2 RN12 FROM CTE ) ,CTE3 AS( SELECT ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],RN12 FROM CTE2 WHERE RN=1 ) SELECT A.BOMID,A.[工艺代码],A.[排序字段],A.ID,B.RN GroupID FROM CTE2 A LEFT JOIN CTE3 B ON A.BOMID=B.BOMID AND A.[工艺代码]=B.[工艺代码] AND A.RN12=B.RN12 ORDER BY A.BOMID,B.[排序字段]
with cte as
(select 99055 as id,1 gno, 7 ono union all
select 99055 as id,2 gno, 13 ono union all
select 99055 as id,4 gno, 34 ono union all
select 99055 as id,4 gno, 43 ono union all
select 99055 as id,4 gno, 67 ono union all
select 99055 as id,8 gno, 70 ono union all
select 99055 as id,7 gno, 82 ono union all
select 99055 as id,9 gno, 94 ono union all
select 99055 as id,4 gno, 97 ono union all
select 104998 as id,1 gno, 1 ono union all
select 104998 as id,2 gno, 2 ono union all
select 104998 as id,4 gno, 3 ono union all
select 104998 as id,4 gno, 4 ono union all
select 104998 as id,10 gno, 5 ono union all
select 104998 as id,4 gno, 6 ono ),
cte1 as
(select *,ROW_NUMBER()over(partition by id order by ono) as n from cte),
cte2 as
(select * ,1 as groupid from cte1 where n=1
union all
select a.*,case when a.gno=b.gno then b.groupid
else b.groupid+1 end as groupid from cte1 as a join cte2 as b
on a.n=b.n+1 and a.id=b.id )
select * from cte2
order by id,ono--结果id gno ono n groupid
----------- ----------- ----------- -------------------- -----------
99055 1 7 1 1
99055 2 13 2 2
99055 4 34 3 3
99055 4 43 4 3
99055 4 67 5 3
99055 8 70 6 4
99055 7 82 7 5
99055 9 94 8 6
99055 4 97 9 7
104998 1 1 1 1
104998 2 2 2 2
104998 4 3 3 3
104998 4 4 4 3
104998 10 5 5 4
104998 4 6 6 5(15 行受影响)
;with acherat as
()
;with acherat as
(
select *,rid=row_number() over (partition by bomid order by 排序字段)
from a
)select *,groupid = dense_rank() over (partition by bomid order by 排序字段 - rid)
from acherat
大版主 你这个用法没见过。
我测试了 结果是这个
[code=sql]
id gno ono rid groupid
----------- ----------- ----------- -------------------- --------------------
99055 1 7 1 1
99055 2 13 2 2
99055 4 34 3 3
99055 4 43 4 4
99055 4 67 5 5
99055 8 70 6 6
99055 7 82 7 7
99055 9 94 8 8
99055 4 97 9 9
104998 1 1 1 1
104998 2 2 2 1
104998 4 3 3 1
104998 4 4 4 1
104998 10 5 5 1
104998 4 6 6 1(15 行受影响)
;with acherat as
(
select *,rid=row_number() over (partition by bomid order by 排序字段),
lid=row_number() over (partition by bomid,工艺代码 order by 排序字段)
from cte1
)
select *,groupid = dense_rank() over (partition by bomid order by lid - rid)
from acherat
--结果
id gno ono rid lid groupid
----------- ----------- ----------- -------------------- -------------------- --------------------
99055 1 7 1 1 1
99055 2 13 2 1 2
99055 4 34 3 1 3
99055 4 43 4 2 3
99055 4 67 5 3 3
99055 8 70 6 1 4
99055 7 82 7 1 5
99055 9 94 8 1 6
99055 4 97 9 4 4
104998 1 1 1 1 1
104998 2 2 2 1 2
104998 4 3 3 1 3
104998 4 4 4 2 3
104998 10 5 5 1 5
104998 4 6 6 3 4(15 行受影响)
我发现你在解决问题是，经常用递归的方式来解决哦。但是觉得递归速度上可能会有问题
我只能想到这个。我也想到了孤岛问题。想想能不能用ROW_NUMBER -number 这种思路来解决。但是没想到。
大版主 你看看这个
99055 1 7 1 1 1
99055 2 13 2 1 2
99055 4 34 3 1 3
99055 4 43 4 2 3
99055 4 67 5 3 3
99055 8 70 6 1 4
99055 7 82 7 1 5
99055 9 94 8 1 6
99055 4 97 9 4 4
你最后一行分组分错了啊
大版主 你看看这个
99055 1 7 1 1 1
99055 2 13 2 1 2
99055 4 34 3 1 3
99055 4 43 4 2 3
99055 4 67 5 3 3
99055 8 70 6 1 4
99055 7 82 7 1 5
99055 9 94 8 1 6
99055 4 97 9 4 4
你最后一行分组分错了啊 这在最后的 order by lid - rid 给这个值乘工艺代码就能分开，不过分不到楼主的效果，看看还有什么方法。
WITH CTE AS(
SELECT
ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN
,ROW_NUMBER()OVER(PARTITION BY [工艺代码] ORDER BY BOMID,[排序字段])RN2
,BOMID,[工艺代码],[排序字段],ID
FROM 表A
)
,CTE2 AS(
SELECT ROW_NUMBER()OVER(PARTITION BY BOMID,[工艺代码],RN-RN2 ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],ID,RN-RN2 RN12 FROM CTE
)
,CTE3 AS(
SELECT ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],RN12 FROM CTE2 WHERE RN=1
)
SELECT
A.BOMID,A.[工艺代码],A.[排序字段],A.ID,B.RN GroupID
FROM
CTE2 A
LEFT JOIN CTE3 B ON A.BOMID=B.BOMID AND A.[工艺代码]=B.[工艺代码] AND A.RN12=B.RN12
ORDER BY A.BOMID,B.[排序字段]