请教如何写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
表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
能举个例子吗
表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
最后的一张表中fnumber=aa的记录,前面两条记录famount是fprice*fqty,但第三条记录如果是fprice*fqty得出的结果为122.但表1中aa总的famount=489,所以最后一笔famount=489-122-244=123了
----------------------------------------------------------------
-- 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
goupdate [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
goupdate 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'/*
fnumber Fqty Fprice Famount id
-------------------------------------------
aa 100 1 122 1
aa 200 1 244 2
aa 100 1 123 3
bb 50 2 111 4
bb 70 2 155 5
*/
--你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
)aselect 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*fqtyupdate 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/*
id Fnumber Fqty Fprice Famout
1 aa 100 1.2220 122
2 aa 200 1.2220 244
3 aa 100 1.2220 123
4 bb 50 2.2220 111
5 bb 70 2.2220 156
*/
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
/* 测试结果如下...
a b c d
-------------------
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
*/
update #b
set Famout=#a.Famout-(select SUM([Famout]) from #b t
where t.fnumber=#b.fnumber and t.id<#b.id)
from #a
where #a.fnumber=#b.fnumber and #b.id in (select MAX(id) from #b group by fnumber)
156=a表中的bbfamount总和267-b表中bb第一条记录111