在线请教：一条SQL语句

[bingge]

请教如何写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

[bingge]

补充一下，表2中有个ID字段，自动递增，判断最后一笔可以用ID值

[szm341]

将所有误差调到每一个fnumber记录中的最后一笔？ 能举个例子吗

[bingge]

可以看到我要的结果表 表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
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'

/*
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有是怎么算来的   我觉得你自己都思维混乱

[szm341]

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

/*
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
*/

[熊猫大虾]

引用 4 楼 TravyLee 的回复:

SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374---------------------------------------……

很好

[szm341]

lee版主的很简洁啊，稍改一下就好了 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)

[bingge]

感谢各位，刚离开了会，我先测试一下。

[bingge]

版主： --你bb的最后一行那个156有是怎么算来的 我觉得你自己都思维混乱 156=a表中的bbfamount总和267-b表中bb第一条记录111

[bingge]

感谢各位，达到目的