树形细表

最新推荐文章于 2023-05-11 14:33:24 发布
别为泼翻的牛奶哭泣
最新推荐文章于 2023-05-11 14:33:24 发布
阅读量161
点赞数
分类专栏： GEPS平台
本文链接：https://blog.csdn.net/weixin_44211388/article/details/103734765
版权
GEPS平台专栏收录该内容
9 篇文章 1 订阅
订阅专栏
--二级单位在建项目;
IF 0!=EXISTSTABLE('#XMJBXX')
THEN
DROP TABLE #XMJBXX;
END;
CREATE TABLE #XMJBXX AS
SELECT 
	sjdw.F_FULL_DEPT_NAME AS DeptName,	
	-XM.F_ID AS F_ID,
	XM.F_SGXMBM_ID AS F_SGXMBM_ID,
	ext.EF_JZMJM AS JZMJ,
	ext.EF_XLCDKM AS LCKM,
	ext.EF_ZJ_K AS EFZJ
	FROM TE_XM_XMJBXX xm
LEFT JOIN T_EXT_XMJBXX ext  ON xm.F_ID = ext.F_EXT_ID 
LEFT JOIN T_ORG_DEPT  sjdw ON sjdw.F_DEPT_ID = xm.F_SGXMBM_ID
where xm.F_PID=0  	 --父子项目去重
AND xm.F_XMZT_K='ZS' 
AND ext.EF_ZJ_K IS NOT NULL;


--二级单位在建项目所有产值;
IF 0!=EXISTSTABLE('#SYCZ')
THEN
DROP TABLE #SYCZ;
END;
CREATE TABLE #SYCZ AS
SELECT 
		-----------------------分组字段-----------------
		-XM.F_ID AS ID,
		-XM.F_ID AS OrderNo,
		------------------------------------------------
		XM.DeptName AS SGJD,              
		czwcqkcx.LJHTJE AS HTE,					        --  合同额
		czwcqkcx.LJWCCZJE AS JZHTE,				        --  结转合同额
		(XM.JZMJ)/10000  AS JZMJ,					    --  建筑面积（万㎡）
		XM.LCKM  AS LCKM,				    		    --  里程
		XM.EFZJ AS EFZJ
FROM #XMJBXX XM 
LEFT JOIN #CZWCQKCX czwcqkcx  ON czwcqkcx.OrgID = xm.F_SGXMBM_ID;
 
-- 取基础施工
IF 0!=EXISTSTABLE('#JCSG')
THEN
DROP TABLE #JCSG;
END;
CREATE TABLE #JCSG AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-1 AS PID,
		'基础施工汇总/基础施工' AS FullCode,
		'基础施工' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 01 ;

-- 取基础施工合计
IF 0!=EXISTSTABLE('#HJJCSG')
THEN
DROP TABLE #HJJCSG;
END;
CREATE TABLE #HJJCSG AS
SELECT  
		---------------分组字段---------------
		-1 AS ID,
		-0 AS PID,
		'基础施工汇总' AS FullCode,
		'基础施工汇总' AS Code,
		0 AS IsLeaf,
		1 AS LevelS,
		1 AS OrderNo,
		-------------------------------------
		'基础施工' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC     
FROM  #JCSG ;

-- 取主体施工
IF 0!=EXISTSTABLE('#ZTSG')
THEN
DROP TABLE #ZTSG;
END;
CREATE TABLE #ZTSG AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-2 AS PID,
		'主体施工汇总/主体施工' AS FullCode,
		'主体施工' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC 								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 02 ; 


-- 取主体施工合计
IF 0!=EXISTSTABLE('#HJZTSG')
THEN
DROP TABLE #HJZTSG;
END;
CREATE TABLE #HJZTSG AS
SELECT
		---------------分组字段---------------
		-2 AS ID,
		-0 AS PID,
		'主体施工汇总' AS FullCode,
		'主体施工汇总' AS Code,
		0 AS IsLeaf,
		1 AS LevelS,
		2 AS OrderNo,
		-------------------------------------
		'主体施工' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC  
FROM #ZTSG;

-- 取二次结构
IF 0!=EXISTSTABLE('#ECJG')
THEN
DROP TABLE #ECJG;
END;
CREATE TABLE #ECJG AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-3 AS PID,
		'二次结构汇总/二次结构' AS FullCode,
		'二次结构' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 03 ;

-- 取合计二次结构
IF 0!=EXISTSTABLE('#HJECJG')
THEN
DROP TABLE #HJECJG;
END;
CREATE TABLE #HJECJG AS
SELECT	
		---------------分组字段---------------
		-3 AS ID,
		-0 AS PID,
		'二次结构汇总' AS FullCode,
		'二次结构汇总' AS Code,
		0 AS IsLeaf,
		1 AS LevelS,
		3 AS OrderNo,
		-------------------------------------
		'二次结构' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC 
FROM #ECJG;

-- 取机电及装饰装修
IF 0!=EXISTSTABLE('#JDJZSZX')
THEN
DROP TABLE #JDJZSZX;
END;
CREATE TABLE #JDJZSZX AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-4 AS PID,
		'机电及装饰装修汇总/机电及装饰装修' AS FullCode,
		'机电及装饰装修' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 04 ;

-- 取合计机电及装饰装修
IF 0!=EXISTSTABLE('#HJJDJZSZX')
THEN
DROP TABLE #HJJDJZSZX;
END;
CREATE TABLE #HJJDJZSZX AS
SELECT	
		---------------分组字段---------------
		-4 AS ID,
		-0 AS PID,
		'机电及装饰装修汇总' AS FullCode,
		'机电及装饰装修汇总' AS Code,
		0 AS IsLeaf,
		1 AS LevelS,
		4 AS OrderNo,
		-------------------------------------
		'机电及装饰装修' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC 
FROM #JDJZSZX;

-- 取停工/缓建
IF 0!=EXISTSTABLE('#TGHJ')
THEN
DROP TABLE #TGHJ;
END;
CREATE TABLE #TGHJ AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-5 AS PID,
		'停工/缓建汇总/停工/缓建' AS FullCode,
		'停工/缓建' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 05 ;

-- 取合计停工/缓建
IF 0!=EXISTSTABLE('#HJTGHJ')
THEN
DROP TABLE #HJTGHJ;
END;
CREATE TABLE #HJTGHJ AS
SELECT 
	---------------分组字段---------------
	-5 AS ID,
	-0 AS PID,
	'停工/缓建汇总' AS FullCode,
	'停工/缓建汇总' AS Code,
	0 AS IsLeaf,
	1 AS LevelS,
	5 AS OrderNo,
	-------------------------------------
		'停工/缓建' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC 
FROM #TGHJ;

-- 取收尾
IF 0!=EXISTSTABLE('#SW')
THEN
DROP TABLE #SW;
END;
CREATE TABLE #SW AS
SELECT 	
		---------------分组字段---------------
		-SYCZ.ID AS ID,
		-6 AS PID,
		'收尾汇总/收尾' AS FullCode,
		'收尾' AS Code,
		1 AS IsLeaf,
		2 AS LevelS,
		SYCZ.OrderNo AS OrderNo,
		-------------------------------------	
		SYCZ.SGJD AS SGJD,					    		--  工程项目名称	
		1 as SL,                                        --  数量                     
		SYCZ.HTE AS HTE,					            --  合同额
		SYCZ.JZHTE AS JZHTE,				            --  结转合同额
		SYCZ.JZMJ AS JZMJ,				    		    --  建筑面积（万㎡）			    		
		SYCZ.LCKM  AS LC								--  里程
FROM #SYCZ SYCZ   
WHERE   SYCZ.EFZJ = 06 ;

-- 取合计收尾
IF 0!=EXISTSTABLE('#HJSW')
THEN
DROP TABLE #HJSW;
END;
CREATE TABLE #HJSW AS
SELECT 
		---------------分组字段---------------
		-6 AS ID,
		-0 AS PID,
		'收尾汇总' AS FullCode,
		'收尾汇总' AS Code,
		0 AS IsLeaf,
		1 AS LevelS,
		6 AS OrderNo,
		-------------------------------------
		'收尾' AS SGJD,
		COUNT(*)  AS SL,
		ISNULL(SUM (ISNULL(HTE , 0.00)), 0.00) as HTE, 
		ISNULL(SUM (ISNULL(JZHTE, 0.00)), 0.00)  as JZHTE,
	    ISNULL(SUM (ISNULL(JZMJ , 0.00)), 0.00) AS JZMJ, 
	    ISNULL(SUM (ISNULL(LC, 0.00)), 0.00)  AS LC 
FROM #SW;

--输出---
SELECT * FROM #HJJCSG 
UNION ALL 
SELECT * FROM #JCSG 
UNION ALL 
SELECT * FROM #HJZTSG
UNION ALL 
SELECT * FROM #ZTSG
UNION ALL 
SELECT * FROM #HJECJG
UNION ALL 
SELECT * FROM #ECJG
UNION ALL 
SELECT * FROM #HJJDJZSZX 
UNION ALL 
SELECT * FROM #JDJZSZX 
UNION ALL 
SELECT * FROM #HJTGHJ
UNION ALL 
SELECT * FROM #TGHJ
UNION ALL 
SELECT * FROM #HJSW
UNION ALL 
SELECT * FROM #SW;