求一条SQL语句(200)

X

xhj12077021

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #1

表：aa(ID,PID,BUYSUM,SALESUM,RESTSUM) 1, 2 0 0 10 2, 4 0 0 200 3, 5 0 0 50 4, 2 10 0 20 5, 5 0 60 -10 6, 4 100 0 300由于各种原因，RESTSUM字段值 经常错误，要进行调整。要求:相同PID，本条RESTSUM=上条RESTSUM+本条BUYSUM-本条SALESUM，

 

X

xhj12077021

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #2

我用的是MYSQL

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #3

Mysql语法更加灵活，oracle的许多语法和sql server的语法，它都支持。再问一下，你提到：本条RESTSUM=上条RESTSUM+本条BUYSUM-本条SALESUM那么对于每个PID第一条记录是如何处理的？因为它没有上一条，是不是不用考虑第一条记录的情况？

 

X

xhj12077021

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #4

对的

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #5

数据库是？Oracle写法 :Create View XZN AsSelect * from 表awhere id in (Select min(ID) from 表a group by pid)update 表a aset RESTSUM = (Select Sum(BUYSUM-SALESUM) from 表a b where b.PID=a.PID and b.id<=a.id) + (Select RESTSUM-(BUYSUM-SALESUM) from XZN b where b.PID=a.PID)where id not in (Select min(ID) from 表a group by pid)其它写法等会儿了

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-25

• #6

本条RESTSUM=上条RESTSUM+本条BUYSUM-本条SALESUM，----------对于第N条记录，其数据为 第2。。N条记录的Sum(BUYSUM-SALESUM)，外加上第一条记录的RESTSUM。测试我上面SQL吧，个别语法错误，别发出来在问。

 

W

wangdonghai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-04-02

• #7

数据库为SQL Server2000在SQL Server2000查询分析器里执行下列语句(按楼主提供的表和字段)Create Function Test(@AID int,@APID int)Returns intAsbegin Declare @Ret int, @ID int Select @ID=(Select Top 1 ID From a where ID<@AID and PID=@APID) if @ID is null Select @ID=@AID Select @Ret=RestSum From a where ID=@ID and PID=@APID return @RetendGOselect *,(BuySum+dbo.Test(ID,PID)-SaleSum) CalcRestSum from a Drop Function Test