一个比较复杂的sql语句如何写

Question

sqlserver2000，表#t1数据如下：
id   product   unit  color   type   quantity    price     amount   totalquantity
1       A       kg    大红    进仓    309.2      14.9           
2       A       kg    黄色    进仓    109.2      15.9
3       A       kg    大红    进仓    201.3      15.2               
4       B       kg    绿色    进仓    291.4      16.4     
5       A       kg    大红    出仓    23.5       14.9
6       A       kg    大红    出仓    23.9
7       A       kg    黄色    出仓    69.2
8       B       kg    绿色    出仓    90.1
9       A       kg    大红    进仓    60我想把#t1数据变为：id   product   unit  color   type   quantity    price     amount   totalquantity
1       A       kg    大红    进仓    309.2      14.9     4607.08      309.2
2       A       kg    黄色    进仓    109.2      15.9     1736.28      109.2
3       A       kg    大红    进仓    201.3      15.2     7666.84      510.5
4       B       kg    绿色    进仓    291.4      16.4     4778.96      291.4
5       A       kg    大红    出仓    23.5       14.9     7316.69      487
6       A       kg    大红    出仓    23.9       15.02    6957.71      463.1
7       A       kg    黄色    出仓    69.2       15.9     636          40        
8       B       kg    绿色    出仓    90.1       16.4     3301.32      201.3
9       A       kg    大红    进仓    60         15.02    7858.91      523.1
说明：
id=1，amount=上一个amount(一定要product、unit、color一定要相同，否则不能相加，当type="进仓"为正数，"出仓"为负数)+309.2*14.9=4607.08，totalquantity=上一个totalquantity(一定要product、unit、color一定要相同，否则不能相加，当type="进仓"为正数，"出仓"为负数)+309.2=309.2id=2，amount=上一个amount(一定要product、unit、color一定要相同，否则不能相加，当type="进仓"为正数，"出仓"为负数)+109.2*15.9=1736.28，totalquantity=上一个totalquantity(一定要product、unit、color一定要相同，否则不能相加，当type="进仓"为正数，"出仓"为负数)+109.2=109.2id=3，amount=上一个amount+201.3*15.2=4607.08+3059.76=7666.84(因为id=1的product、unit、color与id=3的product、unit、color相同，所以相加，当type="进仓"为正数，"出仓"为负数)，totalquantity=上一个totalquantity+201.3=309.2+201.3=510.5(因为id=1的product、unit、
color与id=3的product、unit、color相同，所以相加，当type="进仓"为正数，"出仓"为负数)id=4，amount=上一个amount+amount=0+4778.96=4778.96，totalquantity=上一个totalquantity+quantity=0+291.4=291.4id=5，amount=上一个amount+amount=7666.84(因为最近的id=3的product、unit、color与id=5的product、unit、color相同，所以取id=3的amount=7666.84)-23.5*14.9(因为id=5的type="出仓"，所以为负数)=7316.69，totalquantity=上一个totalquantity+quantity=510.5-23.5=487id=6，price=上一个amount/totalquantity=7316.69/487=15.02(这是加权平均单价，是取最近一次的结存金额除以结存数量，但是product、unit、color一定要相同)，amount=上一个amount+amount=7316.69-15.02*23.9(因为type="出仓"，所以为负数)=6957.71，totalquantity=上一个totalquantity+quantity=487-23.9=463.1id=7，price=上一个amount/totalquantity=1736.28/109.2=15.9(这是加权平均单价，是取最近一次的结存金额除以结存数量，但是product、unit、color一定要相同)，amount=上一个amount+amount=1736.28-69.2*15.9(因为type="出仓"，所以为负数)=636，totalquantity=上一个
totalquantity+quantity=109.2-69.2=40id=8，price=上一个amount/totalquantity=4778.96/291.4=16.4(这是加权平均单价，是取最近一次的结存金额除以结存数量，但是product、unit、color一定要相同)，amount=上一个amount+amount=4778.96-90.1*16.4=3301.32，totalquantity=上一个totalquantity+quantity=291.4-90.1=201.3id=9，price=上一个amount/totalquantity=6957.71/463.1=15.02(这是加权平均单价，是取最近一次的结存金额除以结存数量，但是product、unit、color一定要相同)，amount=上一个amount+amount=6957.71+60*15.02(因为type="进仓"，所以为正数)=7858.91，totalquantity=上一个
totalquantity+quantity=463.1+60=523.1请问如何用sql语句实现#t1没有price、amount和totalquantity的数据，变成有数据？不要用游标来实现，且price、amount和totalquantity取值从id由小到大按顺序来取。请把上面的内容copy到记事本里看，这样比较容易看一点

Answer 1

--检索:
SELECT id,product,unit,color,type,quantity,price,
amount=(
SELECT SUM(ISNULL(b.amount,0)) 
FROM #t b WHERE b.product=a.product AND b.unit=a.unit,a.color=b.color
AND b.id<=a.id
),
totalquantity=
(
SELECT SUM(ISNULL(b.amount,0)*ISNULL(b.quantity,0)) 
FROM #t b WHERE b.product=a.product AND b.unit=a.unit,a.color=b.color
AND b.id<=a.id
)
FROM #t a--更新
UPDATE a SET amount=(
SELECT SUM(ISNULL(b.amount,0)) 
FROM #t b WHERE b.product=a.product AND b.unit=a.unit,a.color=b.color
AND b.id<=a.id
),
totalquantity=
(
SELECT SUM(ISNULL(b.amount,0)*ISNULL(b.quantity,0)) 
FROM #t b WHERE b.product=a.product AND b.unit=a.unit,a.color=b.color
AND b.id<=a.id
)
FROM #t a没有测试，可能有手误，写法就是这样了。

Answer 2

to fcuandy:
不需要测试你的代码，一看代码就是错的，因为amount=上一个amount+当前的amount，totalquantity=上一个totalquantity+当前的quantity，我的说明说得很清楚的

Answer 3

create table qh(id int,product varchar(2),unit varchar(2),color varchar(8),type varchar(8),quantity decimal(18,2),price decimal(18,2))
insert into qh select 
1 ,     'A',       'kg',    '大红',    '进仓',    309.2,    14.9     union all select 
2 ,     'A',       'kg',    '黄色',    '进仓',    109.2,     15.9     union all select 
3 ,     'A',       'kg',    '大红',    '进仓',    201.3,     15.2     union all select               
4 ,     'B',       'kg',    '绿色',    '进仓',    291.4,     16.4     union all select      
5 ,     'A',       'kg',    '大红',    '出仓',    23.5,      14.9     union all select 
6 ,     'A',       'kg',    '大红',    '出仓',    23.9,      null     union all select 
7 ,     'A',       'kg',    '黄色',    '出仓',    69.2,      null     union all select 
8 ,     'B',       'kg',    '绿色',    '出仓',    90.1,      null     union all select 
9 ,     'A',       'kg',    '大红',    '进仓',    60,        null
go--下面价格更新针对null连续并且都在后面才好用，存在穿插的情况要复杂一些
update qh set 
price = (select sum(quantity*price*case type when '进仓' then 1 else -1 end)/sum(quantity*case type when '进仓' then 1 else -1 end) from qh where product=A.product and unit=A.unit and color=A.color and id<A.id and price is not null)
from qh A
where price is null--的得出你要的表
select id,product,unit,color,type,quantity,price,
round((select sum(case when type='进仓' then quantity*price else -1*quantity*price end) from qh where product=A.product and unit=A.unit and color=A.color and id<=A.id),2) amount,
round((select sum(case when type='进仓' then quantity else -1*quantity end) from qh where product=A.product and unit=A.unit and color=A.color and id<=A.id),1) totalquantity
from qh Adrop table qh

Answer 4

fstao() ( ) 信誉：97  2006-08-21 07:06:00  得分: 0  
 
 
   to fcuandy:
不需要测试你的代码，一看代码就是错的，因为amount=上一个amount+当前的amount，totalquantity=上一个totalquantity+当前的quantity，我的说明说得很清楚的
  
 
自连接的方式你去学学,看明白了再说话.
跟生成序数列的道理一样.
tb
id name num
1  aa   99
2  bb   89
3  dd   95
select id,name ,index=(select count(1) from tb b where b.id<=a.id) from tb a
select id,name,allnum=(select sum(b.num) from tb b where b.id<=a.id) from tb a
看懂了这两句再看我的语句.
b.id<=a.id 意思就是合计在它记录前面的包括它自己.=就表示它自己.再不明白我就没话说了.

Answer 5

只是没考滤你是进或出仓,加个CASE WHEN就是了.SUM((CASE b.Type WHEN '进仓' THEN 1 ELSE -1 END )*ISNULL(b.amount,0)) 
下面那个也一样.

Answer 6

amount=price*quantity  需要价格更新 fcuandy() 你没看仔细吧！

Answer 7

只看了他前面几句描述就没看了. 很简单的问题非要写在那样,说一句price是null然后怎么处理不就得了.  但他回贴的意思是 我的生成序数法得到的上一个amount+当前amount的值是错的.