sql语句帮忙还原成表格

2018-07-17 10:36:22 332 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

NVIDIA Jetson Orin系列 - 使用SDK Manager安装JetPack全流程解析本文详细解析了使用NVIDIA官方SDK Manager为Jetson Orin系列开发板安装JetPack系统的完整流程。从环境准备、设备进入恢复模式，到SDK Manager图形化界面的分步操作指南，提供了新手友好的全流程解析，并分享了安装后的验证方法及常见问题解决方案，帮助开发者高效搭建稳定的边缘AI开发环境。阅读详情

好久没搞SQL了，找大神帮忙把这段代码中的字段换成成表格，因为没有源数据库，所以运行不了，想还原下这张表格是什么样的，只要字段就行。



declare @Begintime datetime

declare @Endtime datetime

declare @wh nvarchar(10)

declare @itmsgrpnam nvarchar(30)

declare @wl2 nvarchar(30)

declare @wl3 nvarchar(30)

set @Begintime='[%0]'

set @Endtime = '[%1]'

set @wh=isnull((select   t0.whscode from [dbo].[OWHS] t0  where t0.[Whsname] = '[%2]'),'')

set @itmsgrpnam = '[%3]'



--set @Begintime='2013.04.01'

--set @Endtime = '2013.04.30'

--set @wh = ''





SELECT CONVERT(varchar(100), @Begintime, 102)+'-'+CONVERT(varchar(100), @Endtime, 102) '时间段'

   ,B0.ItemCode '物料编码',B0.Itemname '物料名称',B0.BalInvntAc '库存科目',

       c1.invntryuom '库存单位',

         c2.itmsgrpnam '物料组',

          B0.WhsName '仓库名称'

		,B1.期初数量,

		CASE WHEN B1.期初数量 =0 THEN 0

		ELSE (ISNULL(B1.期初总余额,0))/B1.期初数量 END as '期初单价',

		B1.期初总余额,

		A4.本期入库,

				CASE WHEN A4.本期入库 =0 THEN 0

		ELSE (ISNULL(A8.本期入库金额,0))/A4.本期入库 END as '入库单价',

		A8.本期入库金额,

		A4.本期出库,

		CASE WHEN A4.本期出库 =0 THEN 0

		    ELSE (ISNULL(-A9.本期出库金额,0))/A4.本期出库 END as '出库单价',

		-A9.本期出库金额 '本期出库金额',

		B2.期末数量,

		CASE WHEN B2.期末数量 =0 THEN 0

		ELSE (ISNULL(B1.期初总余额,0)+ISNULL(A8.本期入库金额,0)+ISNULL(A9.本期出库金额,0))/B2.期末数量 END as '期末单价',

		ISNULL(B2.期末总余额,0) AS 期末总余额



FROM 

/********************/

	(select T0.Itemcode,T1.ItemName,T0.WhsCode,T2.WhsName,T2.BalInvntAc

     from [dbo].[oitw] T0 

		INNER JOIN [dbo].[oitm] T1 ON T0.[itemcode]=T1.[itemcode]

		INNER JOIN [dbo].[owhs] T2 ON T0.[WhsCode] = T2.[WhsCode]

		where (T2.WhsCode=@wh  or @wh='')) B0 

/********************/

LEFT JOIN 



/********************/

	(select A1.ItemCode,A1.Warehouse,A1.期初总余额 , A2.期初数量

	from 



		(SELECT  T0.Itemcode 'ItemCode' ,T0.Warehouse 'Warehouse',sum(T0.[TransValue]) '期初总余额'

		FROM [dbo].[oinm] T0 

         where T0.docdate <@Begintime 

		and (T0.Warehouse=@wh  or @wh='')

		group by T0.ItemCode,T0.Warehouse ) A1 

		LEFT JOIN 

		(SELECT T0.ItemCode as 'Itemcode', T0.Warehouse 'Warehouse', sum(ISNULL(T0.InQty,0) - ISNULL(T0.OutQty,0)) as '期初数量' 

		FROM [dbo].[oinm]  T0 WHERE T0.docdate <@Begintime and (T0.Warehouse=@wh  or @wh='') GROUP BY T0.ItemCode,T0.Warehouse ) A2 

		ON  A1.Itemcode=A2.Itemcode and A1.Warehouse =A2.Warehouse 

		) B1 

/********************/

ON B0.Itemcode=B1.Itemcode and B0.WhsCode=B1.Warehouse





LEFT JOIN 

/********************/

	(SELECT T0.ItemCode as '物料编码',T0.Warehouse 'Warehouse', sum(ISNULL(T0.InQty,0)) as '本期入库', sum(ISNULL(T0.OutQty,0)) '本期出库'

	FROM [dbo].[oinm]  T0 WHERE T0.docdate >=@Begintime and  T0.docdate <=@Endtime and (T0.Warehouse=@wh  or @wh='') GROUP BY T0.ItemCode,T0.Warehouse 

	) A4 

/********************/

ON  B0.Itemcode=A4.物料编码 and B0.WhsCode=A4.Warehouse



LEFT JOIN

/********************/

	(SELECT T0.ItemCode as '物料编码',T0.Warehouse 'Warehouse', sum(ISNULL(T0.TransValue,0))  '本期入库金额'

	FROM [dbo].[oinm]  T0 WHERE T0.docdate >=@Begintime and  T0.docdate <=@Endtime and T0.TransValue>=0  and (T0.Warehouse=@wh  or @wh='') 

	GROUP BY T0.ItemCode,T0.Warehouse ) A8

/********************/

ON  B0.Itemcode=A8.物料编码 and B0.WhsCode=A8.Warehouse



LEFT JOIN 

/********************/

	(SELECT T0.ItemCode as '物料编码',T0.Warehouse 'Warehouse', sum(ISNULL(T0.TransValue,0)) '本期出库金额' 

	FROM [dbo].[oinm]  T0 WHERE T0.docdate >=@Begintime and  T0.docdate <=@Endtime and T0.TransValue<0  and (T0.Warehouse=@wh  or @wh='')

	GROUP BY T0.ItemCode,T0.Warehouse ) A9

/********************/

ON  B0.Itemcode=A9.物料编码 and B0.WhsCode=A9.Warehouse



LEFT JOIN 

/********************/

	(select A1.ItemCode,A1.Warehouse,A1.期末总余额,A2.期末数量

	from 



		(SELECT  T0.Itemcode 'ItemCode',T0.Warehouse 'Warehouse', sum(T0.[TransValue]) '期末总余额' 

		FROM [dbo].[oinm] T0 where T0.docdate <=@Endtime 

		and (T0.Warehouse=@wh  or @wh='') group by T0.ItemCode,T0.Warehouse )  A1

		LEFT JOIN 

		(SELECT T0.ItemCode as Itemcode,T0.Warehouse 'Warehouse', sum(ISNULL(T0.InQty,0) - ISNULL(T0.OutQty,0)) as '期末数量' 

		FROM [dbo].[oinm]  T0 

		 WHERE T0.[docdate] <=@Endtime 

		    and (T0.[Warehouse]=@wh  or @wh='') GROUP BY T0.ItemCode,T0.Warehouse ) A2 

		ON  A1.Itemcode=A2.Itemcode and A1.Warehouse=A2.Warehouse ) B2 

/********************/

ON B2.ItemCode=B0.Itemcode  and B0.WhsCode=B2.Warehouse

inner  join [dbo].[oitm] C1   on B0.[ItemCode]=C1.[ItemCode]

inner join [dbo].[OITB] C2  on C1.[ItmsGrpCod]=C2.[ItmsGrpCod]  

where 

(isnull(B1.期初数量,0)<>0 or isnull(A4.本期入库,0)<>0 or isnull(A4.本期出库,0)<>0  or isnull(B2.期末数量,0)<>0

  or ISNULL(B1.期初总余额,0)<>0 or isnull(B2.期末总余额,0)<>0 or ISNULL(A8.本期入库金额,0)<>0 or ISNULL(A9.本期出库金额,0)<>0

 )

 and 

 --B0.itemcode='C.M.S.0170' and B0.WhsName='成品仓（公司）'

--and 

(c2.itmsgrpnam=@itmsgrpnam or @itmsgrpnam ='')