BG_ZC_DM:物品代码与名称设置表。
BG_ZC_BGYP_CK_MAIN: 出库单 主表 (记录物品出库时间)
BG_ZC_BGYP_CK:出库单从表,记录出库单不同物品出库数量。
BG_ZC_BGYP_RK:入库表
查询结果展示字段: 物品名称,库存数量,最近的入库时间,最近的入库价格,最近的出库时间。
select distinct (SELECT max( rtrim(MC)) from BG_ZC_DM d where d.FLDM=a.FLDM ) as FLDM,
sum(isNull(a.SSLL,0))-isNull((select sum(b.SFSL) from BG_ZC_BGYP_CK b ,BG_ZC_BGYP_CK_MAIN x where b.KPH=x.LSH and b.FLDM=a.FLDM and x.FLAG='JS' ),0) as SL,max(a.RKSJ)as RKSJ,
max(a.JG)as JG ,
(select max(z.JBRQ) from BG_ZC_BGYP_CK b,BG_ZC_BGYP_CK_MAIN z where z.LSH=b.KPH and z.FLAG='JS' AND b.FLDM=a.FLDM ) as JBSJ
from
BG_ZC_BGYP_RK a
where
a.FLDM like :fldm
GROUP BY a.FLDM
order by a.FLDM