sql直接账表：领料汇总表（简单生产领料与生产领料同时使用，月底需要汇总）

最近总是遇到客户，既用生产领料，又有部分共耗是简单生产领料的，财务月底总是要两边一起统计，不愿意excel导出匹配一下，所以写了个sql直接账表，我非开发所以写的有不少问题，就当抛砖引玉了

前提：客户是单组织的，统计维度是期间段内，各生产车间从各仓库领用领用情况汇总，统计的单据有生产领料、生产退料、简单生产领料、简单生产退料，版本是cloud 标准版7.7

第一步：写sql，代码如下：

/*
01
我遇到的第一个问题就是讲4个报表关联起来，因为四个表各自的数据并不一一对应，车间、仓库、物料这几个变量
无论用联接还是union都解决不了,所以我想了个笨办法，上来先遍历组合一遍，这个特别占资源，好在这个表一个月
也就用一次，暂时就不考虑性能了，并且后面结果加一些where限定条件速度还行
*/
	select wlandck.FMATERIALID,wlandck.FSTOCKID,T_BD_DEPARTMENT.FDEPTID from t_bd_department
	cross join
	--这里是第二步，wlandck（物料和仓库表）和车间表进行交叉连接，并且筛选出对应字段，留下了
	--物料id,仓库id，部门id
	(select T_BD_MATERIAL.FMATERIALID,T_BD_STOCK.FSTOCKID from T_BD_MATERIAL
	cross join T_BD_STOCK) as wlandck
	--这里是第一步，先把物料和仓库做一遍交叉连接，然后作为wlandck（物料和仓库表），再一次和车间表交叉连接
	where T_BD_DEPARTMENT.FDEPTPROPERTY='4866f13a3a3940b9b2fe47895a6e7cbe'
	--这里是第三步，防止数据过大做一些限定条件，我这里限定了生产车间
	
/*
02
有了上面的表作为基表，接下去处理每一个领料、退料的表的汇总，以简单生产领料为例，这里没有用表别名，
看起来会累赘点，其他三表都是相同原理。
*/
        select T_SP_PICKMTRL.FID AS T_SP_PICKMTRLFID,T_SP_PICKMTRLDATA.FMATERIALID AS T_SP_PICKMTRLDATA物料ID,T_SP_PICKMTRLDATA.FSTOCKID AS T_SP_PICKMTRLDATA仓库ID,T_SP_PICKMTRL.FWORKSHOPID as T_SP_PICKMTRL车间ID,sum(T_SP_PICKMTRLDATA.FACTUALQTY) as T_SP_PICKMTRLDATA数量 from T_SP_PICKMTRL
	--这里是第一步将简单生产领料表作为主表，搜索对应字段，注意这里用了sum聚合领料数量
	left join T_SP_PICKMTRLDATA ON T_SP_PICKMTRLDATA.FID=T_SP_PICKMTRL.FID
	--这里是第二步将简单生产领料表和简单生产领料明细表左联接
	WHERE T_SP_PICKMTRL.FDATE>='#FDateS#' AND T_SP_PICKMTRL.FDATE<='#FDateE#'
	--这里是第三步就是限定了一下单据的时间范围，其中#FDateS#、#FDateE#是金蝶的关键字，
	--这里没用变量处理，变量会报错
	group by  T_SP_PICKMTRL.FID,T_SP_PICKMTRLDATA.FMATERIALID,T_SP_PICKMTRLDATA.FSTOCKID,T_SP_PICKMTRL.FWORKSHOPID
	--这里是第四步，按照车间、仓库、物料id聚合
	
/*
03
所有的基础表好了之后，以01的搜索结果作为表头，去做关联其他四个表，以车间、仓库、物料id唯一匹配，下附原表
*/
select 
	bt.FMATERIALID,m.FNUMBER as 物料编码,ml.fname as 物料名称,ml.FSPECIFICATION as 规格型号,d.FNUMBER as  车间编码,dl.FNAME as 车间名称,s.FNUMBER as 仓库编码,sl.FNAME as 仓库名称,
	isnull(jdll.T_SP_PICKMTRLDATA数量,0) as 简单生产领料,isnull((0-jdtl.简单生产退料合计数量),0) as 简单生产退料,isnull(scll.生产领料合计数,0) as 生产领料,isnull((0-sctl.生产退料合计数量),0) as 生产退料,
	(isnull(jdll.T_SP_PICKMTRLDATA数量,0)-isnull(jdtl.简单生产退料合计数量,0)+isnull(scll.生产领料合计数,0)-isnull(sctl.生产退料合计数量,0)) as 本期领用
from--搜索开始
--先罗列所有物料组合
	(
	select wlandck.FMATERIALID,wlandck.FSTOCKID,T_BD_DEPARTMENT.FDEPTID from t_bd_department
	cross join
	(select T_BD_MATERIAL.FMATERIALID,T_BD_STOCK.FSTOCKID from T_BD_MATERIAL
	cross join T_BD_STOCK) as wlandck
	where T_BD_DEPARTMENT.FDEPTPROPERTY='4866f13a3a3940b9b2fe47895a6e7cbe'
	) as bt
--简单生产领料left join
left join
	(
	select T_SP_PICKMTRL.FID AS T_SP_PICKMTRLFID,T_SP_PICKMTRLDATA.FMATERIALID AS T_SP_PICKMTRLDATA物料ID,T_SP_PICKMTRLDATA.FSTOCKID AS T_SP_PICKMTRLDATA仓库ID,T_SP_PICKMTRL.FWORKSHOPID as T_SP_PICKMTRL车间ID,sum(T_SP_PICKMTRLDATA.FACTUALQTY) as T_SP_PICKMTRLDATA数量 from T_SP_PICKMTRL
	left join T_SP_PICKMTRLDATA ON T_SP_PICKMTRLDATA.FID=T_SP_PICKMTRL.FID
	WHERE T_SP_PICKMTRL.FDATE>='#FDateS#' AND T_SP_PICKMTRL.FDATE<='#FDateE#'
	--and T_SP_PICKMTRLDATA.FMATERIALID=102131   --测试用
	group by  T_SP_PICKMTRL.FID,T_SP_PICKMTRLDATA.FMATERIALID,T_SP_PICKMTRLDATA.FSTOCKID,T_SP_PICKMTRL.FWORKSHOPID
	) as jdll
on jdll.T_SP_PICKMTRLDATA物料ID=bt.FMATERIALID and jdll.T_SP_PICKMTRLDATA仓库ID=bt.FSTOCKID and jdll.T_SP_PICKMTRL车间ID=bt.FDEPTID

--简单生产退料left join
left join
(
select T_SP_RETURNMTRLENTRY.FMATERIALID as 物料id,T_SP_RETURNMTRL.FWORKSHOPID as 生产车间ID,T_SP_RETURNMTRLENTRY.FSTOCKID as 仓库id,sum(T_SP_RETURNMTRLENTRY.FQTY) as 简单生产退料合计数量
from T_SP_RETURNMTRLENTRY
left join T_SP_RETURNMTRL on T_SP_RETURNMTRL.fid=T_SP_RETURNMTRLENTRY.fid
where T_SP_RETURNMTRLENTRY.fid in 
(select fid from T_SP_RETURNMTRL WHERE T_SP_RETURNMTRL.FDATE>='#FDateS#'  AND T_SP_RETURNMTRL.FDATE<='#FDateE#')
group by T_SP_RETURNMTRLENTRY.FMATERIALID,T_SP_RETURNMTRLENTRY.FSTOCKID,T_SP_RETURNMTRL.FWORKSHOPID
) as jdtl
on jdtl.物料id=bt.FMATERIALID and jdtl.仓库id=bt.FSTOCKID and jdtl.生产车间ID=bt.FDEPTID

--生产领料left join
left join
(
select  T_PRD_PICKMTRLDATA.FMATERIALID as 物料ID,T_PRD_PICKMTRLDATA.FWORKSHOPID as 生产车间id,T_PRD_PICKMTRLDATA.FSTOCKID as 仓库ID,sum(T_PRD_PICKMTRLDATA.FACTUALQTY) as 生产领料合计数
from T_PRD_PICKMTRLDATA
 where T_PRD_PICKMTRLDATA.fid in
(select T_PRD_PICKMTRL.fid from T_PRD_PICKMTRL WHERE T_PRD_PICKMTRL.FDATE>='#FDateS#' AND T_PRD_PICKMTRL.FDATE<='#FDateE#')
group by  T_PRD_PICKMTRLDATA.FMATERIALID,T_PRD_PICKMTRLDATA.FWORKSHOPID,T_PRD_PICKMTRLDATA.FSTOCKID
) as scll
on scll.仓库ID=bt.FSTOCKID and scll.生产车间id=bt.FDEPTID and scll.物料ID=bt.FMATERIALID

--生产退料left join 
left join 
(
select T_PRD_RETURNMTRLENTRY.FMATERIALID as 物料ID, T_PRD_RETURNMTRLENTRY.FWORKSHOPID as 生产车间ID,T_PRD_RETURNMTRLENTRY.FSTOCKID as 仓库ID,sum(T_PRD_RETURNMTRLENTRY.FQTY) as 生产退料合计数量
from  T_PRD_RETURNMTRLENTRY
where T_PRD_RETURNMTRLENTRY.fid in
(select fid from T_PRD_RETURNMTRL WHERE T_PRD_RETURNMTRL.FDATE>='#FDateS#' AND T_PRD_RETURNMTRL.FDATE<='#FDateE#')
group by  T_PRD_RETURNMTRLENTRY.FMATERIALID, T_PRD_RETURNMTRLENTRY.FWORKSHOPID,T_PRD_RETURNMTRLENTRY.FSTOCKID
) as sctl
on sctl.仓库ID=bt.FSTOCKID and sctl.生产车间ID=bt.FDEPTID and sctl.物料ID=bt.FMATERIALID
--把基础资料信息加载过来
left join T_BD_MATERIAL m on bt.FMATERIALID=m.FMATERIALID
left join T_BD_MATERIAL_l ml on bt.FMATERIALID=ml.FMATERIALID
left join T_BD_STOCK  s on bt.FSTOCKID=s.FSTOCKID
left join T_BD_STOCK_l sl on bt.FSTOCKID=sl.FSTOCKID
left join T_BD_DEPARTMENT d on bt.FDEPTID=d.FDEPTID
left join T_BD_DEPARTMENT_l dl on bt.FDEPTID=dl.FDEPTID
--限定条件
where jdll.T_SP_PICKMTRLDATA数量>0 or jdtl.简单生产退料合计数量>0 or scll.生产领料合计数>0 or sctl.生产退料合计数量>0
--order by m.FNUMBER asc,d.FNUMBER asc,s.FNUMBER asc

当前该表存在的问题：

A基表数据过大；

B无法进行排序操作（发布到前台，order居然报错）；

C当前日期其实一开始是声明变量的，但是发布到前台，金蝶也报错；

D该表不适用多组织，尤其库存组织与生产组织不一致的时候；

E该表当前不能显示期初期末

反正问题很多，凑合能用，有大佬可以教教我就太好了

另外附上sql直接账表发布的链接

https://vip.kingdee.com/article/197756589615498496