代码段如下:declare @Period nvarchar(7)
select @Period = t0.code from ofpr t0 where t0.code = '2007-09'select
max(zx.ItmsGrpNam) N'物料组',zz.itemcode N'物料号',max(zy.ItemName) N'物料名称',sum(zz.begqty) N'期初数量',sum(zz.begtotal) N'期初金额',
sum(inqty) N'本月入库数量',sum(intotal) N'本月入库金额',sum(outqty) N'本月出库数量',sum(outtotal) N'本月出库金额',
sum(endqty) N'期未数量',sum(endtotal) N'期未余额'
from (
select
a.ItemCode,sum(a.InQty-a.OutQty) begqty,sum(a.TransValue) begtotal,
0 inqty,0 intotal,0 outqty,0 outtotal,0 [color=#00FF00]endqty,0 endtotal[/color]
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.F_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,sum(a.InQty),sum(case when a.TransValue>=0 then a.TransValue else 0 end),
sum(a.OutQty),sum(case when a.TransValue<0 then a.TransValue else 0 end),0,0
from oinm a,ofpr b where b.Code = @Period and a.CreateDate >= b.F_RefDate and a.CreateDate <= b.T_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,0,0,0,0,sum(a.InQty-a.OutQty),sum(a.TransValue)
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.T_RefDate
group by a.ItemCode) zz,
oitm zy,
oitb zx
where zz.itemcode = zy.ItemCode and zy.ItmsGrpCod = zx.ItmsGrpCod
group by zz.itemcode代码执行正常,有数据得出!
问题一:红色标示出的字段,前面加一个0是什么意思?
问题二:绿色标示出的字段,在代码中出现的表中都没有找到,这个字段是怎么生成的?
select @Period = t0.code from ofpr t0 where t0.code = '2007-09'select
max(zx.ItmsGrpNam) N'物料组',zz.itemcode N'物料号',max(zy.ItemName) N'物料名称',sum(zz.begqty) N'期初数量',sum(zz.begtotal) N'期初金额',
sum(inqty) N'本月入库数量',sum(intotal) N'本月入库金额',sum(outqty) N'本月出库数量',sum(outtotal) N'本月出库金额',
sum(endqty) N'期未数量',sum(endtotal) N'期未余额'
from (
select
a.ItemCode,sum(a.InQty-a.OutQty) begqty,sum(a.TransValue) begtotal,
0 inqty,0 intotal,0 outqty,0 outtotal,0 [color=#00FF00]endqty,0 endtotal[/color]
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.F_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,sum(a.InQty),sum(case when a.TransValue>=0 then a.TransValue else 0 end),
sum(a.OutQty),sum(case when a.TransValue<0 then a.TransValue else 0 end),0,0
from oinm a,ofpr b where b.Code = @Period and a.CreateDate >= b.F_RefDate and a.CreateDate <= b.T_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,0,0,0,0,sum(a.InQty-a.OutQty),sum(a.TransValue)
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.T_RefDate
group by a.ItemCode) zz,
oitm zy,
oitb zx
where zz.itemcode = zy.ItemCode and zy.ItmsGrpCod = zx.ItmsGrpCod
group by zz.itemcode代码执行正常,有数据得出!
问题一:红色标示出的字段,前面加一个0是什么意思?
问题二:绿色标示出的字段,在代码中出现的表中都没有找到,这个字段是怎么生成的?
解决方案 »
- MS AdoDataGrid (SP6) 控件,记录非常多时,拖动不起作用,有什么办法,或合适的控件吗?
- 关于sql语句的问题
- 添加外键约束不成功。我该如何做?如何查出是哪些不符合约束?
- 在VB中如何引用SQL Server中执行DTS包时的成功和错误信息(急.急.急.急)
- 不使用企业管理器如果实现修改表结构?
- 事件833错误,谁知道怎么解决
- number类型怎么转化为时间,谢谢!
- 继续提问 高分求动态sql
- 如何把这个interbase触发器(生成器)写成MSQL触发器,100分,在线等候,马上给分,谢谢!!!!!!!
- 请问在客户端提供多条记录填写表单的代码???????
- 存储过程中若同时有insert和select 语句,为何ado不能获得select 语句的查询?
- 我的数据库名是mydata,如何测出数据库mydata的大小,SQL语句如何写?
declare @Period nvarchar(7)
select @Period = t0.code from ofpr t0 where t0.code = '2007-09 ' select
max(zx.ItmsGrpNam) N '物料组 ',zz.itemcode N '物料号 ',max(zy.ItemName) N '物料名称 ',sum(zz.begqty) N '期初数量 ',sum(zz.begtotal) N '期初金额 ',
sum(inqty) N '本月入库数量 ',sum(intotal) N '本月入库金额 ',sum(outqty) N '本月出库数量 ',sum(outtotal) N '本月出库金额 ',
sum(endqty) N '期未数量 ',sum(endtotal) N '期未余额 '
from (
select
a.ItemCode,sum(a.InQty-a.OutQty) begqty,sum(a.TransValue) begtotal,
0 as inqty,0 as intotal,0 as outqty,0 as outtotal,0 as endqty,0 as endtotal
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.F_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,sum(a.InQty),sum(case when a.TransValue >=0 then a.TransValue else 0 end),
sum(a.OutQty),sum(case when a.TransValue <0 then a.TransValue else 0 end),0,0
from oinm a,ofpr b where b.Code = @Period and a.CreateDate >= b.F_RefDate and a.CreateDate <= b.T_RefDate
group by a.ItemCode
union all
select
a.ItemCode,0,0,0,0,0,0,sum(a.InQty-a.OutQty),sum(a.TransValue)
from oinm a,ofpr b where b.Code = @Period and a.CreateDate < b.T_RefDate
group by a.ItemCode) zz,
oitm zy,
oitb zx
where zz.itemcode = zy.ItemCode and zy.ItmsGrpCod = zx.ItmsGrpCod
group by zz.itemcode