【PostGresql】------ pg多表数据多个条件汇总使用 union 方法示例代码

皮皮冰要做大神

已于 2024-03-25 10:00:13 修改

阅读量363

点赞数 1

分类专栏： # PostGresql 文章标签：数据库 postgresql sql

于 2024-03-20 10:30:00 首次发布

本文链接：https://blog.csdn.net/qq_38366657/article/details/136868654

版权

PostGresql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

1. 示例代码如下：

SELECT
		"ID",
		"DT_DATE",
		"CNAME",
		"RMAN_NAME",
		"DEP_NAME",
		"DEP_ID",
		"INVEST_MAN_NAME",
		"TYPE_NAME",
		"INVEST_LEVEL_NAME",
		"POSITION_NAME",
		"CMEMO",
	  SUM ( "YHCOUNT" ) AS "YHCOUNT",
		SUM ( "YCCOUNT" ) AS "YCCOUNT",
		SUM ( "WCCOUNT" ) AS "WCCOUNT"

FROM
	(
		(
				SELECT
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO",
					COUNT ( "APT"."WR_ID" ) AS "YHCOUNT",
					0 AS "YCCOUNT",
					0 AS "WCCOUNT"
				FROM
					"DCS_WR" AS "WR"
					LEFT JOIN "DCS_ACCIDENT_POTENTIAL" AS "APT" ON "WR"."ID" = "APT"."WR_ID" 
				WHERE
					"WR"."BILL_TYPE" = '隐患排查记录' 
					AND "WR"."ORG_ID" = '23cffbbf076a49798171ea9afbc25330' 
					AND ( "WR"."DT_DATE" >= '2024-02-18' ) 
					AND ( "WR"."DT_DATE" <= '2024-03-20' ) 
				GROUP BY
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO" 
				
			) UNION
			(
				SELECT
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO",
					0 AS "YHCOUNT",
					COUNT ( "APT"."WR_ID" ) AS "YCCOUNT",
					0 AS "WCCOUNT"
				FROM
					"DCS_WR" AS "WR"
					LEFT JOIN "DCS_ACCIDENT_POTENTIAL" AS "APT" ON "WR"."ID" = "APT"."WR_ID" 
				WHERE
					"WR"."BILL_TYPE" = '隐患排查记录' 
					AND "WR"."ORG_ID" = '23cffbbf076a49798171ea9afbc25330' 
					AND ( "WR"."DT_DATE" >= '2024-02-18' ) 
					AND ( "WR"."DT_DATE" <= '2024-03-20' ) 
					and  ("APT"."STATUS_NAME"='已复查' or "APT"."STATUS_NAME"='待复查')
				GROUP BY
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO" 
				
			) 
			 UNION
			(
				SELECT
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO",
					0 AS "YHCOUNT",
					0 AS "YCCOUNT",
					COUNT ( "APT"."WR_ID" ) AS "WCCOUNT"
				FROM
					"DCS_WR" AS "WR"
					LEFT JOIN "DCS_ACCIDENT_POTENTIAL" AS "APT" ON "WR"."ID" = "APT"."WR_ID" 
				WHERE
					"WR"."BILL_TYPE" = '隐患排查记录' 
					AND "WR"."ORG_ID" = '23cffbbf076a49798171ea9afbc25330' 
					AND ( "WR"."DT_DATE" >= '2024-02-18' ) 
					AND ( "WR"."DT_DATE" <= '2024-03-20' ) 
					and  "APT"."STATUS_NAME"!='已复查' AND "APT"."STATUS_NAME"!='待复查'
				GROUP BY
					"WR"."ID",
					"WR"."DT_DATE",
					"WR"."CNAME",
					"WR"."RMAN_NAME",
					"WR"."DEP_NAME",
					"WR"."DEP_ID",
					"WR"."INVEST_MAN_NAME",
					"WR"."TYPE_NAME",
					"WR"."INVEST_LEVEL_NAME",
					"WR"."POSITION_NAME",
					"WR"."CMEMO" 
				
			) 
	) "T"
	

WHERE
	( 1 = 1 )   and ("YHCOUNT">0 OR "WCCOUNT">0 OR "YCCOUNT">0)

GROUP BY 
	
		"ID",
		"DT_DATE",
		"CNAME",
		"RMAN_NAME",
		"DEP_NAME",
		"DEP_ID",
		"INVEST_MAN_NAME",
		"TYPE_NAME",
		"INVEST_LEVEL_NAME",
		"POSITION_NAME",
		"CMEMO"
ORDER BY
	"DT_DATE" DESC