27,579
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1(ID int, OrderID varchar(10), Weight numeric(18, 6), AutoID int,
TotalWeight numeric(18,6), GroupID int);
GO
INSERT INTO tb1(ID, OrderID, Weight, AutoID, TotalWeight)
SELECT 3339, '12800389', 0.0238, 1, 0.0238 UNION ALL
SELECT 3339, '12800272', 0.045, 2, 0.0688 UNION ALL
SELECT 3339, '12800267', 0.133, 3, 0.2018 UNION ALL
SELECT 3339, '12800254', 0.75, 4, 0.9518 UNION ALL
SELECT 3339, '12800344', 0.365, 5, 1.3168 UNION ALL
SELECT 3339, '12800315', 0.047, 6, 1.3638 UNION ALL
SELECT 3339, '12800298', 0.137, 7, 1.5008 UNION ALL
SELECT 3339, '12794841', 0.765, 8, 2.2658 UNION ALL
SELECT 3339, '12794837', 0.765, 9, 3.0308 UNION ALL
SELECT 3339, '12786045', 0.8656, 10, 3.8964 UNION ALL
SELECT 3339, '12786042', 0.8656, 11, 4.762 UNION ALL
SELECT 3339, '12800679', 0.0332, 12, 4.7952 UNION ALL
SELECT 3339, '12800677', 0.0351, 13, 4.8303 UNION ALL
SELECT 3339, '12800633', 0.0577, 14, 4.888 UNION ALL
SELECT 3339, '12800602', 0.027, 15, 4.915;
--*****************************************************
--利用游标解决
declare @aID int, @wei numeric(18,6);
declare @GroupID int;
declare @sumWeight numeric(18,6);
declare @tmpT table(AutoID int, GroupID int);
set @sumWeight = 0;
set @GroupID = 0;
declare C CURSOR fast_forward for select AutoID, [Weight] from tb1 order by AutoID;
open C
fetch Next From C into @aID, @wei;
while @@FETCH_STATUS = 0
begin
set @sumWeight = @sumWeight + @wei;
if @sumWeight > 1.5
begin
set @GroupID = @GroupID + 1;
insert into @tmpT(AutoID, GroupID) Values(@aID, @GroupID);
set @sumWeight = @wei;
end
else begin
insert into @tmpT(AutoID, GroupID) Values(@aID, @GroupID);
end
fetch Next From C into @aID, @wei;
end
Close C;
Deallocate C;
--更新原表的GroupID数据
update a
set a.GroupID = b.GroupID
from tb1 a
join @tmpT b
on a.AutoID = b.AutoID;
select * from Tb1;
/*
ID OrderID Weight AutoID TotalWeight GroupID
----------- ---------- ---------- ----------- ------------ -----------
3339 12800389 0.023800 1 0.023800 0
3339 12800272 0.045000 2 0.068800 0
3339 12800267 0.133000 3 0.201800 0
3339 12800254 0.750000 4 0.951800 0
3339 12800344 0.365000 5 1.316800 0
3339 12800315 0.047000 6 1.363800 0
3339 12800298 0.137000 7 1.500800 1
3339 12794841 0.765000 8 2.265800 1
3339 12794837 0.765000 9 3.030800 2
3339 12786045 0.865600 10 3.896400 3
3339 12786042 0.865600 11 4.762000 4
3339 12800679 0.033200 12 4.795200 4
3339 12800677 0.035100 13 4.830300 4
3339 12800633 0.057700 14 4.888000 4
3339 12800602 0.027000 15 4.915000 4
*/