B
brink
Unregistered / Unconfirmed
GUEST, unregistred user!
表RK(入库表)
字段 产品代码,入库数量,入库日期,入库单号
表CK(出库表)
字段 产品代码,出库数量,出库方式,出库日期,领用人,出库单号
表QCKC(期初库存)
字段 产品代码,期初数量
表FZ(产品表)
字段 产品代码,产品名称,规格型号
如何根据产品代码,统计出如下的月报表,有何简便的方法,
产品代码,产品名称,规格型号,上月库存,本月入库,本月出库,本月库存
select FZ.FZ_FZBM ,FZ.FZ_FZMC,FZ.FZ_GGXH,FZ.FZ_FZLB,
sum(case when QCKC_KCSL>0 then isnull(QCKC.QCKC_KCSL,0) else 0 end )+
sum(case when RK.RK_RKRQ< '2002-04-01' then isnull(RK.RK_RKSL,0) else 0 end )-
sum(case when CK.CK_CKRQ< '2002-04-01' then isnull(CK.CK_CKSL,0) else 0 end ) as 上月库存,
sum(case when RK.RK_RKRQ between '2002-04-01' and '2002-04-30' then isnull(RK.RK_RKSL,0) else 0 end ) as 本月入库,
sum(case when CK.CK_CKRQ between '2002-04-01' and '2002-04-30' then isnull(CK.CK_CKSL,0) else 0 end ) as 本月出库,
sum(case when QCKC_KCSL>0 then isnull(QCKC.QCKC_KCSL,0) else 0 end )+
sum(case when RK.RK_RKRQ<= '2002-04-30' then isnull(RK.RK_RKSL,0) else 0 end )-
sum(case when CK.CK_CKRQ<= '2002-04-30' then isnull(CK.CK_CKSL,0) else 0 end ) as 本月库存
from FZ
left join RK on FZ.FZ_FZBM=RK.RK_FZBM (???)
left join CK on FZ.FZ_FZBM=CK.CK_FZBM (???)
left join QCKC on FZ.FZ_FZBM=QCKC.QCKC_FZBM (???)
group by FZ.FZ_FZBM,FZ.FZ_FZMC,FZ.FZ_FZLB,FZ.FZ_GGXH,QCKC.QCKC_KCSL
当入库,出库表中对同一产品有多条入,出库记录时,统计时就重复计算。一对多
如何解决
字段 产品代码,入库数量,入库日期,入库单号
表CK(出库表)
字段 产品代码,出库数量,出库方式,出库日期,领用人,出库单号
表QCKC(期初库存)
字段 产品代码,期初数量
表FZ(产品表)
字段 产品代码,产品名称,规格型号
如何根据产品代码,统计出如下的月报表,有何简便的方法,
产品代码,产品名称,规格型号,上月库存,本月入库,本月出库,本月库存
select FZ.FZ_FZBM ,FZ.FZ_FZMC,FZ.FZ_GGXH,FZ.FZ_FZLB,
sum(case when QCKC_KCSL>0 then isnull(QCKC.QCKC_KCSL,0) else 0 end )+
sum(case when RK.RK_RKRQ< '2002-04-01' then isnull(RK.RK_RKSL,0) else 0 end )-
sum(case when CK.CK_CKRQ< '2002-04-01' then isnull(CK.CK_CKSL,0) else 0 end ) as 上月库存,
sum(case when RK.RK_RKRQ between '2002-04-01' and '2002-04-30' then isnull(RK.RK_RKSL,0) else 0 end ) as 本月入库,
sum(case when CK.CK_CKRQ between '2002-04-01' and '2002-04-30' then isnull(CK.CK_CKSL,0) else 0 end ) as 本月出库,
sum(case when QCKC_KCSL>0 then isnull(QCKC.QCKC_KCSL,0) else 0 end )+
sum(case when RK.RK_RKRQ<= '2002-04-30' then isnull(RK.RK_RKSL,0) else 0 end )-
sum(case when CK.CK_CKRQ<= '2002-04-30' then isnull(CK.CK_CKSL,0) else 0 end ) as 本月库存
from FZ
left join RK on FZ.FZ_FZBM=RK.RK_FZBM (???)
left join CK on FZ.FZ_FZBM=CK.CK_FZBM (???)
left join QCKC on FZ.FZ_FZBM=QCKC.QCKC_FZBM (???)
group by FZ.FZ_FZBM,FZ.FZ_FZMC,FZ.FZ_FZLB,FZ.FZ_GGXH,QCKC.QCKC_KCSL
当入库,出库表中对同一产品有多条入,出库记录时,统计时就重复计算。一对多
如何解决