救我！！！200分！！！(100分)

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #1

我现在遇到个难题！！！
表结构为：
ID(流水号),product_id,QTY1,date,
要求的到如下的查询结果：
product_id, QTY1,(QTY2),date
1,B , 10 , 10, 1-1 初始化
2,B , 10 , 20, 1-2 此行QTY2=上一行QTY2+此行QTY!
3,B , 20 , 40, 1-3 同上
______________________
4,D , 30 , 30, 1-1　同上
5,D , 40 , 70, 1-2
6,D , 50 ,120, 1-3
______________________，
请问这样的查询如何实现？计算字段好像实现不了？
如果哪位兄台能够帮助小弟，200分赠高手，如果不够可以再加！！！

 

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #2

不好意思，积分一时匆忙打错了，
帮我解决的了问题，分数好说啊！

 

B

bes96261

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #3

select * into #Tem from Table1
alter table #Tem add QTY2 float
Update #Tem set QTY2=IsNull(QTY2,0)+(select Sum(QTY1) from Table1 where ID>=#Tem.ID)
select * from #Tem
Drop table #Tem

現將你的表設名為Table1 返回的#Tem表應該是你所想要的

 

无

无忧鱼

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #4

用临时表吧，将记录一条条插入到一个临时表，再读出。

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #5

Select product_id,
date,
(Select sum(qty1) from Table1 where date =AAA.date and product_id=AAA.product_id) as Qty1,
(Select sum(qty1) from Table1 where date<=AAA.date and product_id=AAA.product_id) as Qty2
From (Select distinct product_id,date
From Table1
Order by product_id,date) AAA

试试

 

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #6

thanks!
I know!
转贴，我给你们加分！

 

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #7

好像还有个问题……
如果要求product_id相同，date相同,不合并能否实现？
即Table1查询结果不变，实现上述功能！

 

B

bes96261

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #8

不知你將採用那一套方案，不過原理都差不多：
Update #Tem set QTY2=IsNull(QTY2,0)+(select Sum(QTY1) from Table1 where ID>=#Tem.ID)
將where條件改一下：where product_id=#Temp.product_id and Date=#Tem.Date and ID>=#Tem.ID

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #9

可，通过流水号来判断

Select product_id,
date,
(Select qty1 from Table1 where id=AAA.id),
(Select sum(qty1) from Table1 where date<=AAA.date and
product_id=AAA.product_id and
id<=AAA.id) as Qty2
From (Select id,product_id,date
From Table1
Order by product_id,date,id) AAA

另外那边的分我已拿了，这里的分给bes96261兄吧

 

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #10

thanks

 

T

tingliuxingyu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #11

thanks