--试试
create table #t(料号 int, 工序号 int, 工时 int,flag int)
insert #t(料号,工序号,工时)
select 101, 10, 20 union all
select 101, 20, 26 union all
select 101, 30, 45 union all
select 101, 40, 80 union all
select 101, 70, 15 union all
select 101, 80, 23 union all
select 102, 10, 10 union all
select 102, 20, 25 union all
select 102, 30, 46 union all
select 102, 60, 20 union all
select 102, 70, 23
declare @料号 int,@工序号 int,@工时 int,@flag int
set @flag = 0
update #t
set @flag = case when 料号 = @料号 and 工序号 - @工序号 <= 10 then @flag else @flag + 1 end
,@料号 = 料号,@工序号 = 工序号,flag = @flag select 料号,min(工序号),sum(工时) from #t group by 料号,flagdrop table #t料号
----------- ----------- -----------
101 10 171
101 70 38
102 10 81
102 60 43(所影响的行数为 4 行)
create table #t(料号 int, 工序号 int, 工时 int,flag int)
insert #t(料号,工序号,工时)
select 101, 10, 20 union all
select 101, 20, 26 union all
select 101, 30, 45 union all
select 101, 40, 80 union all
select 101, 70, 15 union all
select 101, 80, 23 union all
select 102, 10, 10 union all
select 102, 20, 25 union all
select 102, 30, 46 union all
select 102, 60, 20 union all
select 102, 70, 23
declare @料号 int,@工序号 int,@工时 int,@flag int
set @flag = 0
update #t
set @flag = case when 料号 = @料号 and 工序号 - @工序号 <= 10 then @flag else @flag + 1 end
,@料号 = 料号,@工序号 = 工序号,flag = @flag select 料号,min(工序号),sum(工时) from #t group by 料号,flagdrop table #t料号
----------- ----------- -----------
101 10 171
101 70 38
102 10 81
102 60 43(所影响的行数为 4 行)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货