select ll.barcode,ll.quantity-mm.quantity as rest from (select * from tab t where fid in (select top 1 fid from tab where barcode=t.barcode order by fid) ) ll inner join (select * from tab t where fid in (select top 1 fid from tab where barcode=t.barcode order by fid desc)) mm on ll.barcode=mm.barcode
select barcode ,(select max(quantity) from T where barcode = a.barcode and fid = (select min(fid) from T where barcode = a.barcode ) ) - (select max(quantity) from T where barcode = a.barcode and fid = (select max(fid) from T where barcode = a.barcode ) )from T a group by barcode
delete T from T a where fid > (select min(fid) from T where barcode = a.barcode ) and fid < (select max(fid) from T where barcode = a.barcode )
select a.barcode,quantity=a.quantity-b.quantity from( select barcode,quantity from 表 a where Fid= (select min(Fid) from 表 where barcode=a.barcode) ) a join( select barcode,quantity from 表 a where Fid= (select max(Fid) from 表 where barcode=a.barcode) ) b on a.barcode=b.barcode
--下面是测试--测试数据 declare @表 table(Fid int,barcode char(13),quantity decimal(20,4)) insert into @表 select 4,'1000000000320',12.0000 union all select 5,'1000000000320',12.0000 union all select 6,'1000000000320',12.0000 union all select 7,'1000000000320',11.0000 union all select 16,'1000000013054',12.0000 union all select 19,'1000000013054',12.0000 union all select 23,'1000000013054',12.0000 union all select 24,'1000000013054',12.0000 union all select 25,'1000000013054',12.0000 union all select 26,'1000000013054',12.0000 union all select 27,'1000000013054',12.0000 union all select 28,'1000000013054',12.0000 union all select 29,'1000000013054',11.3000 union all select 30,'1000000020001',12--查询处理 select a.barcode,quantity=a.quantity-b.quantity from( select barcode,quantity from @表 a where Fid= (select min(Fid) from @表 where barcode=a.barcode) ) a join( select barcode,quantity from @表 a where Fid= (select max(Fid) from @表 where barcode=a.barcode) ) b on a.barcode=b.barcode/*--测试结果 barcode quantity ------------- ----------------------- 1000000000320 1.0000 1000000013054 .7000 1000000020001 .0000(所影响的行数为 3 行)--*/
--如果是要删除原表的数据,就用:--更新处理 update 表 set quantity=b.quantity from 表 a join( select b.Fid,quantity=a.quantity-b.quantity from( select barcode,quantity from 表 a where Fid= (select min(Fid) from 表 where barcode=a.barcode) ) a join( select Fid,barcode,quantity from 表 a where Fid= (select max(Fid) from 表 where barcode=a.barcode) ) b on a.barcode=b.barcode ) b on a.Fid=b.Fid--删除重复的数据 delete 表 from 表 a where fid<>(select max(fid) from 表 where barcode=a.barcode)
--下面是测试--测试数据 declare @表 table(Fid int,barcode char(13),quantity decimal(20,4)) insert into @表 select 4,'1000000000320',12.0000 union all select 5,'1000000000320',12.0000 union all select 6,'1000000000320',12.0000 union all select 7,'1000000000320',11.0000 union all select 16,'1000000013054',12.0000 union all select 19,'1000000013054',12.0000 union all select 23,'1000000013054',12.0000 union all select 24,'1000000013054',12.0000 union all select 25,'1000000013054',12.0000 union all select 26,'1000000013054',12.0000 union all select 27,'1000000013054',12.0000 union all select 28,'1000000013054',12.0000 union all select 29,'1000000013054',11.3000 union all select 30,'1000000020001',12--更新处理 update @表 set quantity=b.quantity from @表 a join( select b.Fid,quantity=a.quantity-b.quantity from( select barcode,quantity from @表 a where Fid= (select min(Fid) from @表 where barcode=a.barcode) ) a join( select Fid,barcode,quantity from @表 a where Fid= (select max(Fid) from @表 where barcode=a.barcode) ) b on a.barcode=b.barcode ) b on a.Fid=b.Fid--删除重复的数据 delete @表 from @表 a where fid<>(select max(fid) from @表 where barcode=a.barcode)--显示处理: select * from @表/*--测试结果 Fid barcode quantity ----------- ------------- ---------------------- 7 1000000000320 1.0000 29 1000000013054 .7000 30 1000000020001 .0000(所影响的行数为 3 行) --*/
统计barcode前后两次的差
barcode=t.barcode order by fid) ) ll inner join (select * from tab t where fid in (select top 1 fid from tab where
barcode=t.barcode order by fid desc)) mm on ll.barcode=mm.barcode
,(select max(quantity)
from T
where barcode = a.barcode
and fid = (select min(fid)
from T
where barcode = a.barcode
)
)
-
(select max(quantity)
from T
where barcode = a.barcode
and fid = (select max(fid)
from T
where barcode = a.barcode
)
)from T a
group by barcode
from T a
where fid > (select min(fid)
from T
where barcode = a.barcode
)
and
fid < (select max(fid)
from T
where barcode = a.barcode
)
from(
select barcode,quantity from 表 a where Fid=
(select min(Fid) from 表 where barcode=a.barcode)
) a join(
select barcode,quantity from 表 a where Fid=
(select max(Fid) from 表 where barcode=a.barcode)
) b on a.barcode=b.barcode
declare @表 table(Fid int,barcode char(13),quantity decimal(20,4))
insert into @表
select 4,'1000000000320',12.0000
union all select 5,'1000000000320',12.0000
union all select 6,'1000000000320',12.0000
union all select 7,'1000000000320',11.0000
union all select 16,'1000000013054',12.0000
union all select 19,'1000000013054',12.0000
union all select 23,'1000000013054',12.0000
union all select 24,'1000000013054',12.0000
union all select 25,'1000000013054',12.0000
union all select 26,'1000000013054',12.0000
union all select 27,'1000000013054',12.0000
union all select 28,'1000000013054',12.0000
union all select 29,'1000000013054',11.3000
union all select 30,'1000000020001',12--查询处理
select a.barcode,quantity=a.quantity-b.quantity
from(
select barcode,quantity from @表 a where Fid=
(select min(Fid) from @表 where barcode=a.barcode)
) a join(
select barcode,quantity from @表 a where Fid=
(select max(Fid) from @表 where barcode=a.barcode)
) b on a.barcode=b.barcode/*--测试结果
barcode quantity
------------- -----------------------
1000000000320 1.0000
1000000013054 .7000
1000000020001 .0000(所影响的行数为 3 行)--*/
update 表 set quantity=b.quantity
from 表 a join(
select b.Fid,quantity=a.quantity-b.quantity
from(
select barcode,quantity from 表 a where Fid=
(select min(Fid) from 表 where barcode=a.barcode)
) a join(
select Fid,barcode,quantity from 表 a where Fid=
(select max(Fid) from 表 where barcode=a.barcode)
) b on a.barcode=b.barcode
) b on a.Fid=b.Fid--删除重复的数据
delete 表
from 表 a where fid<>(select max(fid) from 表 where barcode=a.barcode)
declare @表 table(Fid int,barcode char(13),quantity decimal(20,4))
insert into @表
select 4,'1000000000320',12.0000
union all select 5,'1000000000320',12.0000
union all select 6,'1000000000320',12.0000
union all select 7,'1000000000320',11.0000
union all select 16,'1000000013054',12.0000
union all select 19,'1000000013054',12.0000
union all select 23,'1000000013054',12.0000
union all select 24,'1000000013054',12.0000
union all select 25,'1000000013054',12.0000
union all select 26,'1000000013054',12.0000
union all select 27,'1000000013054',12.0000
union all select 28,'1000000013054',12.0000
union all select 29,'1000000013054',11.3000
union all select 30,'1000000020001',12--更新处理
update @表 set quantity=b.quantity
from @表 a join(
select b.Fid,quantity=a.quantity-b.quantity
from(
select barcode,quantity from @表 a where Fid=
(select min(Fid) from @表 where barcode=a.barcode)
) a join(
select Fid,barcode,quantity from @表 a where Fid=
(select max(Fid) from @表 where barcode=a.barcode)
) b on a.barcode=b.barcode
) b on a.Fid=b.Fid--删除重复的数据
delete @表
from @表 a where fid<>(select max(fid) from @表 where barcode=a.barcode)--显示处理:
select * from @表/*--测试结果
Fid barcode quantity
----------- ------------- ----------------------
7 1000000000320 1.0000
29 1000000013054 .7000
30 1000000020001 .0000(所影响的行数为 3 行)
--*/