【PostGresql】-------PG实现按单位的设备名称汇总并按单位合计汇总然后实现集团对各个单位的汇总合计sql查询统计

SELECT
	"T".* 
FROM
	(
	
	SELECT
		wx."TBDW",
		zy."SBMX",
		CAST ( zy."YYLT" AS NUMERIC ),
		CAST ( zy."SJKDSC" AS NUMERIC ),
		CAST ( zy."YKDSC" AS NUMERIC ),
		CAST ( zy."WDYXL" AS NUMERIC ) 
	FROM
		"JT_HBSSYZQK" "wx"
		LEFT JOIN "JT_HBSSYZQK_HBSSYZQK" "zy" ON zy."MASTER_DATA_ID" = wx."ID" 
	WHERE
		1 = 1 
		AND ( "wx"."ORG_ID" = '942d70f64f0d4fe38645b77a855addc6' ) 
		AND TO_DATE( wx."NY", 'YYYY-MM-DD' ) BETWEEN '2025-01-01' 
		AND '2025-04-30'
	
	UNION ALL
	SELECT
		wx."TBDW",
		'环保设施合计' "SBMX",
		SUM ( CAST ( zy."YYLT" AS NUMERIC ) ) AS "YYLT",
		SUM ( CAST ( zy."SJKDSC" AS NUMERIC ) ) AS "SJKDSC",
		SUM ( CAST ( zy."YKDSC" AS NUMERIC ) ) AS "YKDSC",
		AVG ( CAST ( zy."WDYXL" AS NUMERIC ) ) AS "WDYXL" 
	FROM
		"JT_HBSSYZQK" "wx"
		LEFT JOIN "JT_HBSSYZQK_HBSSYZQK" "zy" ON zy."MASTER_DATA_ID" = wx."ID" 
	WHERE
		1 = 1 
		AND ( "wx"."ORG_ID" = '942d70f64f0d4fe38645b77a855addc6' ) 
		AND TO_DATE( wx."NY", 'YYYY-MM-DD' ) BETWEEN '2025-01-01' 
		AND '2025-04-30' 
	GROUP BY
		wx."TBDW" 
		
		UNION ALL
			SELECT
				'合计' "TBDW",
				zy."SBMX",
				SUM ( CAST ( zy."YYLT" AS NUMERIC ) ) AS "YYLT",
				SUM ( CAST ( zy."SJKDSC" AS NUMERIC ) ) AS "SJKDSC",
				SUM ( CAST ( zy."YKDSC" AS NUMERIC ) ) AS "YKDSC",
				AVG ( CAST ( zy."WDYXL" AS NUMERIC ) ) AS "WDYXL" 
			FROM
				"JT_HBSSYZQK" "wx"
				LEFT JOIN "JT_HBSSYZQK_HBSSYZQK" "zy" ON zy."MASTER_DATA_ID" = wx."ID" 
			WHERE
				1 = 1 
				AND ( "wx"."ORG_ID" = '942d70f64f0d4fe38645b77a855addc6' ) 
				AND TO_DATE( wx."NY", 'YYYY-MM-DD' ) BETWEEN '2025-01-01' 
				AND '2025-04-30' 
			GROUP BY
				zy."SBMX" 

			UNION ALL
				SELECT 
				  '合计' "TBDW",
				  '设备设施合计' "SBMX",
					SUM ( CAST ( zy."YYLT" AS NUMERIC ) ) AS "YYLT",
					SUM ( CAST ( zy."SJKDSC" AS NUMERIC ) ) AS "SJKDSC",
					SUM ( CAST ( zy."YKDSC" AS NUMERIC ) ) AS "YKDSC",
					AVG ( CAST ( zy."WDYXL" AS NUMERIC ) ) AS "WDYXL" 
				FROM
					"JT_HBSSYZQK" "wx"
					LEFT JOIN "JT_HBSSYZQK_HBSSYZQK" "zy" ON zy."MASTER_DATA_ID" = wx."ID" 
				WHERE
					1 = 1 
					AND ( "wx"."ORG_ID" = '942d70f64f0d4fe38645b77a855addc6' ) 
					AND TO_DATE( wx."NY", 'YYYY-MM-DD' ) BETWEEN '2025-01-01' 
					AND '2025-04-30' 
	) "T" 
ORDER BY
	"T"."TBDW"  desc,
	"T"."SBMX" 
	LIMIT 500 OFFSET 0