|
|
本帖最后由 linmeici 于 2012-3-29 12:58 编辑
本方法适用于较少量品种的数据计算,(太多品种的计算速度会很慢,尽量不多于20个品种)如果是大量数据计算还是最好使用循环计算.
代码如下:
n_union = "select 序号,名称,库存数,0 as 购入数,0 as 领用数 from [库存$] union all select 序号,名称,0 as 库存数,购入数,0 as 领用数 from [购入$]union all select 序号,名称,0 as 库存数,0 as 购入数,领用数 from [领用$]"
Sql = "select 序号,名称,购入数,领用数,"
Sql = Sql & "(select (sum(库存数)+sum(购入数)-sum(领用数)) from ( " & n_union & ") where 名称=a.名称 and 序号<=a.序号) as 库存数量 "
Sql = Sql & " from (" & n_union & " order by 名称,序号) a"
计算结果:
序号 | 名称 | 购入数 | 领用数 | 库存数量 | a3 | 夹克 | - | - | 50,000.00 | b3 | 夹克 | 120.00 | - | 50,120.00 | b6 | 夹克 | 530.00 | - | 50,650.00 | c3 | 夹克 | - | 4,745.00 | 45,905.00 | c6 | 夹克 | - | 1,072.00 | 44,833.00 | a1 | T恤 | - | - | 4,060.00 | b1 | T恤 | 3,200.00 | - | 7,260.00 | b4 | T恤 | 1,450.00 | - | 8,710.00 | c1 | T恤 | - | 2,310.00 | 6,400.00 | c4 | T恤 | - | 1,296.00 | 5,104.00 | a2 | 衬衣 | - | - | 1,620.00 | b2 | 衬衣 | 12,000.00 | - | 13,620.00 | b5 | 衬衣 | 530.00 | - | 14,150.00 | c2 | 衬衣 | - | 3,715.00 | 10,435.00 | c5 | 衬衣 | - | 3,995.00 | 6,440.00 |
具体内容请看附件:
SQL购销存明细及库存计算.rar
(15.95 KB, 下载次数: 133)
|
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-3-28 08:46
