我有数据如下
ID typeID num
0001 01 10.20
0001 02 10.40
0001 03 10.40
0002 01 20.00
0002 02 10.00
0003 03 20.50
0003 04 10.50
如何判断将每个id 有小数据取整,要求是typeID最大的余数全部汇总到此数据,要求结果如下
ID typeID num
0001 01 10.00
0001 02 10.00
0001 03 11.00
0002 01 20.00
0002 02 10.00
0003 03 20.00
0003 04 11.00
--方法
--Ken Wong
--测试数据
create table tb (ID varchar(4),typeID varchar(2),num dec(4,2))
insert into tb
select '0001','01',10.20 union all
select '0001','02',10.40 union all
select '0001','03',10.40 union all
select '0002','01',20.00 union all
select '0002','02',10.00 union all
select '0003','03',20.50 union all
select '0003','04',10.50
--执行
update tb set
num = case when typeID = (select max(typeID) from tb where t.id = id) then
cast(cast(tb.num as int) as dec(4,2)) + t.余数
else cast(cast(tb.num as int) as dec(4,2)) end
from tb ,(select ID,sum(num - cast(cast(num as int) as dec(4,2))) as 余数
from tb group by ID) t
where tb.id = t.id --and typeID = (select max(typeID) from tb where t.id = id)
select * from tb
drop table tb
--结果
0001 01 10.00
0001 02 10.00
0001 03 11.00
0002 01 20.00
0002 02 10.00
0003 03 20.00
0003 04 11.00