【无标题】多表结果合并UNION ALL 和 FULL JOIN

UNION ALL

SELECT
	hospId,
	hospName,
	SUM( outNumber ) AS outNumber,
	sum( inNumber ) AS inNumber,
	SUM( unNumber ) AS unNumber 
FROM
	(
	SELECT
		CHANGE_HOSP_ID AS hospId,
		CHANGE_HOSP_NAME AS hospName,
		COUNT( 1 ) AS outNumber,
		0 AS inNumber,
		0 AS unNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS != 3 
	GROUP BY
		CHANGE_HOSP_ID,
		CHANGE_HOSP_NAME UNION ALL
	SELECT
		ACCEPT_HOST_ID AS hospId,
		ACCEPT_HOSP_NAME AS hospName,
		0 AS outNumber,
		COUNT( 1 ) AS inNumber,
		0 AS unNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS = 1 
	GROUP BY
		ACCEPT_HOST_ID,
		ACCEPT_HOSP_NAME UNION ALL
	SELECT
		ACCEPT_HOST_ID AS hospId,
		ACCEPT_HOSP_NAME AS hospName,
		0 AS outNumber,
		0 AS inNumber,
		COUNT( 1 ) AS unNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS = 2 
	GROUP BY
		ACCEPT_HOST_ID,
		ACCEPT_HOSP_NAME 
	) 
GROUP BY
	hospId,
	hospName

FULL JOIN

WITH tab1 AS (
	SELECT
		CHANGE_HOSP_ID AS hospId,
		CHANGE_HOSP_NAME AS hospName,
		COUNT( 1 ) AS outNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS != 3 
	GROUP BY
		CHANGE_HOSP_ID,
		CHANGE_HOSP_NAME 
	),
	tab2 AS (
	SELECT
		ACCEPT_HOST_ID AS hospId,
		ACCEPT_HOSP_NAME AS hospName,
		COUNT( 1 ) AS inNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS = 1 
	GROUP BY
		ACCEPT_HOST_ID,
		ACCEPT_HOSP_NAME 
	),
	tab3 AS (
	SELECT
		ACCEPT_HOST_ID AS hospId,
		ACCEPT_HOSP_NAME AS hospName,
		COUNT( 1 ) AS unNumber 
	FROM
		CHRONIC_VISIT_PATIENT_FEEDBACK 
	WHERE
		STATUS = 2 
	GROUP BY
		ACCEPT_HOST_ID,
		ACCEPT_HOSP_NAME 
	) SELECT
	nvl(
		tab1.HOSPID,
	nvl( tab2.HOSPID, tab3.HOSPID )) AS HOSPID,
	nvl(
		tab1.HOSPNAME,
	nvl( tab2.HOSPNAME, tab3.HOSPNAME )) AS HOSPNAME, 
	tab1.outNumber,tab2.INNUMBER,tab3.unNumber
	FROM tab1
	FULL JOIN tab2 ON tab1.HOSPID = tab2.HOSPID
	FULL JOIN tab3 ON tab2.HOSPID = tab3.HOSPID