在一表单中有如下数据:
co_num co_line seq act_date item price_conv qty_conv u_m
216 1 1 2009-5-27 F1000S 0 1 PCS
216 1 2 2009-5-27 F1000S-0053 0 2 PCS
216 1 3 2009-5-27 F1000S-0053 72.4074 0 PCS
216 1 4 2009-5-31 F1000S-0053 -11.55 0 PCS
216 1 5 2009-6-1 F1000S-0053 0 -1 PCS
216 1 6 2009-6-1 F1000S-0053 -20 0 PCS
216 1 7 2009-6-2 F1000S-0053 30 3 PCS
216 1 8 2009-6-2 F1000S-0053 0 0 PCS如何得到下列结果:
co_num co_line seq act_date item price_conv qty_conv u_m
216 1 3 2009-5-27 F1000S-0053 72.4074 3 PCS
216 1 4 2009-5-31 F1000S-0053 60.8574 3 PCS
216 1 5 2009-6-1 F1000S-0053 40.8574 2 PCS
216 1 7 2009-6-2 F1000S-0053 70.8574 5 PCS
co_num co_line seq act_date item price_conv qty_conv u_m
216 1 1 2009-5-27 F1000S 0 1 PCS
216 1 2 2009-5-27 F1000S-0053 0 2 PCS
216 1 3 2009-5-27 F1000S-0053 72.4074 0 PCS
216 1 4 2009-5-31 F1000S-0053 -11.55 0 PCS
216 1 5 2009-6-1 F1000S-0053 0 -1 PCS
216 1 6 2009-6-1 F1000S-0053 -20 0 PCS
216 1 7 2009-6-2 F1000S-0053 30 3 PCS
216 1 8 2009-6-2 F1000S-0053 0 0 PCS如何得到下列结果:
co_num co_line seq act_date item price_conv qty_conv u_m
216 1 3 2009-5-27 F1000S-0053 72.4074 3 PCS
216 1 4 2009-5-31 F1000S-0053 60.8574 3 PCS
216 1 5 2009-6-1 F1000S-0053 40.8574 2 PCS
216 1 7 2009-6-2 F1000S-0053 70.8574 5 PCS
AS
select * from 表 where qty_conv>1
From tb
select * from 表 where qty_conv >0
AS
select * from 表 where qty_conv>1 order by qty_conv desc
Table字段如下:cnum line seq act_date item price qty
216 1 1 2009-5-27 F1000S 0 1
216 1 2 2009-5-27 F1000S-0053 0 2
216 1 3 2009-5-27 F1000S-0053 72.4074 0
216 1 4 2009-5-31 F1000S-0053 -11.55 0
216 1 5 2009-6-1 F1000S-0053 0 -1
216 1 6 2009-6-1 F1000S-0053 -20 0
216 1 7 2009-6-2 F1000S-0053 30 3
216 1 8 2009-6-2 F1000S-0053 0 0 如何得到下列结果:
num line seq act_date item price qty
216 1 3 2009-5-27 F1000S-0053 72.4074 3
216 1 4 2009-5-31 F1000S-0053 60.8574 3
216 1 5 2009-6-1 F1000S-0053 40.8574 2
216 1 7 2009-6-2 F1000S-0053 70.8574 5
就是按日期分组后计算Price和qty的值,同一天如果有多行分录,需要显示每一次修改后的数据
和当天最后的数据.
就是按日期分组后计算Price和qty的值 功能實現:
select num ,line, seq, act_date, item,price ,qty ,sum(price),sum(qty)
from Table
group by num ,line, seq, act_date, item,price ,qty
select num ,line, seq, act_date, item,sum(price) as price ,sum(qty) as qty
from Table
group by num ,line, seq, act_date, item,price ,qty
num line seq act_date item price qty
216 1 1 5-27 F1000S 0 1
216 1 2 5-27 F1000S-0053 0 2
216 1 3 5-27 F1000S-0053 72.4074 0
216 1 4 5-31 F1000S-0053 -11.55 0
216 1 5 6-1 F1000S-0053 0 -1
216 1 6 6-1 F1000S-0053 -20 0
216 1 7 6-2 F1000S-0053 30 3
216 1 8 6-2 F1000S-0053 0 0
说明: 5-27 price由0增加72.4074,最后的结果是72.4074, qty是1后再增加2,也就是1,qty最后的结果是3
5-31 price由72.4074减少11.55,最后的结果是60.8574, qty仍然是3
6-1 price由60.8574减少20,结果是40.8574, qty减少1,结果是2
6-2 price由40.8574增加30,结果是70.8574, qty增加3,结果是5
需求结果是:
num line seq act_date item price qty
216 1 3 5-27 F1000S-0053 72.4074 3
216 1 4 5-31 F1000S-0053 60.8574 3
216 1 5 6-1 F1000S-0053 40.8574 2
216 1 7 6-2 F1000S-0053 70.8574 5