【PostGresql】----- sql多表关联按条件统计汇总代码完整示例

 1. 汇总需求：

    对企业所上传目录类型文件数量及状态进行统计汇总

    ORG_DEPARTMENT : 企业表

    PUB_FILES : 文件表

1.1 代码示例如下： 

SELECT T.*,
CASE WHEN T."FILECOUNT" > 0 THEN'已' ELSE'未' END "FLAG",
CASE WHEN T."FILECOUNT" >= 0 THEN '其他与“三同时”项目相关的文件' ELSE'其他与“三同时”项目相关的文件' END "MLNAME" 
FROM
	(
		(
		SELECT
			"ORG"."ID",
			"ORG"."CNAME",
			"ORG"."PNAME",
			COUNT ( "PUB"."ID" ) AS "FILECOUNT" 
		FROM
			"ORG_DEPARTMENT" AS "ORG"
			LEFT JOIN "PUB_FILES" AS "PUB" ON "ORG"."ID" = "PUB"."PUBLISH_DEP_ID" 
			AND "PUB"."PID" = '87044f8960a943ca9af830d987ebf2bf' 
			AND "PUB"."TYPE" = '安全生产清单制' 
		WHERE
			( 1 = 1 ) 
			AND ( COALESCE ( "ORG"."IS_MGR_DPT", 0 ) != 1 AND COALESCE ( "ORG"."IS_CONTRACTOR_ORG", 0 ) != 1 ) 
		GROUP BY
			"ORG"."ID",
			"ORG"."CNAME",
			"ORG"."PNAME" 
		ORDER BY  "ORG"."CODEPATH" ASC 
		) 
	) T 
ORDER BY  "FILECOUNT" DESC

1.2  sql代码注释图： 

// T 是临时表

SELECT T.*,

// case when then else 是条件判断  类似于  if  else 
// END + 字段名

CASE WHEN T."FILECOUNT" > 0 THEN'已' ELSE'未' END "FLAG",
CASE WHEN T."FILECOUNT" >= 0 THEN '其他与“三同时”项目相关的文件' ELSE'其他与“三同时”项目相关的文件' END "MLNAME" 

FROM
	(
		(

		SELECT
			+汇总字段,
			COUNT (字段名或1) AS "FILECOUNT" 
		FROM
			表1 AS "ORG"
			LEFT JOIN 表2 AS "PUB" ON +两表关联对应条件
		WHERE
			+条件语句
		GROUP BY
			+汇总字段
		ORDER BY  +排序
		) 
	) T 
ORDER BY  +排序

​

   看懂要会灵活变通

2.查询效果图：