库存表:clkuid号:cl_class 库存数量:cl_num 最低库存数:cl_num2cl_class cl_num cl_num2
1 3 1
2 5 2
3 9 8
4 6 3
5 4 2出货记录表: clchuid号:clchu_id 库存id号:cl_class 出库数量:clchu_num 出库日期:clchu_dateclchu_id cl_class clchu_num clchu_date
1 2 1 2012-1-1
2 3 2 2012-3-2
3 5 2 2012-6-3
4 3 2 2012-7-6
5 2 1 2012-9-28
通过sql语句更新 最低库存数=每件商品最近9个月出库总数 每月平均值 例如:库存id:3 9个月内总出库了4个 最低库存数=4/9 也就是 0.4444 把这个值更新到 最低库存数:cl_num2中请大家帮我想一想,有没有通过一句 sql语句,全部更新完的,我知会通过 datareader 一条一条更新,效率低,占资源大
1 3 1
2 5 2
3 9 8
4 6 3
5 4 2出货记录表: clchuid号:clchu_id 库存id号:cl_class 出库数量:clchu_num 出库日期:clchu_dateclchu_id cl_class clchu_num clchu_date
1 2 1 2012-1-1
2 3 2 2012-3-2
3 5 2 2012-6-3
4 3 2 2012-7-6
5 2 1 2012-9-28
通过sql语句更新 最低库存数=每件商品最近9个月出库总数 每月平均值 例如:库存id:3 9个月内总出库了4个 最低库存数=4/9 也就是 0.4444 把这个值更新到 最低库存数:cl_num2中请大家帮我想一想,有没有通过一句 sql语句,全部更新完的,我知会通过 datareader 一条一条更新,效率低,占资源大
= (select sum(clchu_num) where clchu_date between
getdate() and dateadd(mm,9,getdate()) group by cl_class) from clku, clchu
where clku.cl_class = clchu.cl_class
这样写试试
//9月前的日期
string startDate=DateTime.Now.AddMonths(-9).ToString() ;
//现在日期
string endDate=DateTime.Now.ToString() ;update clku set cl_num2
= (select sum(clchu_num)/9 where clchu_date between startDate and endDate)
from clku, clchu where clku.cl_class = clchu.cl_class
update clku set cl_num2
= (select sum(clchu_num)/9 from clchu where cl_class=clku.cl_class and clchu_date between startDate and endDate)
from clku, clchu where clku.cl_class = clchu.cl_class