在线请教：一条SQL语句
请教如何写SQL：有如下2个表：
表1： Fnumber Fprice Fqty Famout 其中（famout=fprice*fqty)但做了四舍五入
aa 1.222 400 489
bb 2.222 120 267
表2： fnumber Fqty Fprice Famount
aa 100
aa 200
aa 100
bb 50
bb 70
要求如下： 将表1中的单价全部更新到表2中，由于存在误差，将所有误差调到每一个fnumber记录中的最后一笔。两表中的famount相等。得到结果如下：
表2： fnumber Fqty Fprice Famount
aa 100 1.222 122
aa 200 1.222 244
aa 100 1.222 123
bb 50 2.222 111
bb 70 2.222 156
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2013-01-16 13:49:28
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null
drop table [a]
go
create table [a]
(
[Fnumber] varchar(2),
[Fprice] numeric(4,3),
[Fqty] int,
[Famout] int
)
insert [a]
select 'aa',1.222,400,489 union all
select 'bb',2.222,120,267
--> 测试数据：[b]
if object_id('[b]') is not null
drop table [b]
go
create table [b]
(
[fnumber] varchar(2),
[Fqty] int,
[Fprice] int,
[Famount] int
)
insert [b]
select 'aa',100,null,null union all
select 'aa',200,null,null union all
select 'aa',100,null,null union all
select 'bb',50,null,null union all
select 'bb',70,null,null
go
update [b]
set [Fprice]=a.[Fprice],[Famount]=floor(b.Fqty*a.Fprice)
from [a]
where b.fnumber=a.Fnumber
alter table b
add id int identity
go
update b
set [Famount]=a.Famout-(select SUM([Famount]) from b t
where t.fnumber=b.fnumber and t.id<b.id)
from [a]
where b.id=(select MAX(id) from b s where b.fnumber=s.fnumber)
and b.fnumber='aa'
/*
fnumberFqtyFpriceFamountid
-------------------------------------------
aa10011221
aa20012442
aa10011233
bb5021114
bb7021555
*/
--你bb的最后一行那个156有是怎么算来的 我觉得你自己都思维混乱
select * into #a from
(
select 'aa' Fnumber,1.222 Fprice,400 Fqty,489 Famout
union all select 'bb' Fnumber,2.222 Fprice,120 Fqty,267 Famout
)a
select identity(int,1,1)id,* into #b from
(
select 'aa' Fnumber,100 Fqty,convert(decimal(10,4),0) Fprice,0 Famout
union all select 'aa' Fnumber,200 Fqty,0 Fprice,0 Famout
union all select 'aa' Fnumber,100 Fqty,0 Fprice,0 Famout
union all select 'bb' Fnumber,50 Fqty,0 Fprice,0 Famout
union all select 'bb' Fnumber,70 Fqty,0 Fprice,0 Famout
)a
update b set b.fprice=a.fprice from #a a inner join #b b on a.fnumber=b.fnumber
update #b set famout=fprice*fqty
update b set b.famout=a.famout from
(select a.fnumber,a.famout-b.famout as famout from #a a inner join
(select fnumber,sum(famout)famout from #b where id not in (select max(id) from #b group by fnumber) group by fnumber)b
on a.fnumber=b.fnumber)a
inner join #b b on a.fnumber=b.fnumber
where b.id in (select max(id) from #b group by fnumber)
select * from #a
select * from #b
/*
idFnumberFqtyFpriceFamout
1aa1001.2220122
2aa2001.2220244
3aa1001.2220123
4bb502.2220111
5bb702.2220156
*/
with tb (id,a,b,c,d) as (
select 1 ,'aa', 100, 1.222, 122 union all
select 2 ,'aa', 200, 1.222, 244 union all
select 3 ,'aa', 100, 1.222, 123 union all
select 4 ,'bb', 50 , 2.222, 111 union all
select 5 ,'bb', 70 , 2.222, 156
) ,
tc as(
select row_number() over(partition by a order by id)number,a,b,c,floor(b*c)aa,count(a) over(partition by a) number1
,sum(b*c) over(partition by a) sum1 from tb
),
td as(
select a,sum(aa)aa from tc where number!=number1 group by a
)
select tc.a,b,c,case when number!=number1 then tc.aa else sum1-td.aa end d
from tc,td where tc.a=td.a
/* 测试结果如下...
abc d
-------------------
aa1001.222122
aa2001.222244
aa1001.222123
bb502.222111
bb702.222156
*/
