postgresql知识点（一）：部门递归

向下递归

WITH RECURSIVE TESTTABLE (
	TEMPID,
	TEMPNAME,
	TEMPPID,
	SORTBY
) AS (
		SELECT '0' TEMPID, 'TEST1' TEMPNAME,'' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '1' TEMPID, 'TEST1-1' TEMPNAME,'0' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '2' TEMPID, 'TEST1-2' TEMPNAME,'0' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '3' TEMPID, 'TEST1-3' TEMPNAME,'0' TEMPPID, 3 SORTBY
		UNION ALL
		SELECT '11' TEMPID, 'TEST1-1-1' TEMPNAME,'1' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '12' TEMPID, 'TEST1-1-2' TEMPNAME,'1' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '13' TEMPID, 'TEST1-1-3' TEMPNAME,'1' TEMPPID, 3 SORTBY
),T AS (
	SELECT
		D1.TEMPID,
		D1.TEMPNAME,
		D1.TEMPPID,
		ARRAY [D1.SORTBY] AS TEMPPATH,
		1 AS DEPTH
	FROM
		TESTTABLE D1
	WHERE
		D1.TEMPPID = ''
		OR D1.TEMPPID IS NULL
	UNION ALL 
	SELECT
		D.TEMPID,
		D.TEMPNAME,
		D.TEMPPID,
		T.TEMPPATH || D.SORTBY TEMPPATH,
		T.DEPTH + 1 AS DEPTH
	FROM
		TESTTABLE D
	JOIN T ON
		D.TEMPPID = T .TEMPID
) 
SELECT TEMPID, TEMPPATH,
	(
		CASE
			WHEN DEPTH<2 THEN '<>'
			ELSE LPAD (
				' ',
				DEPTH * 1,
				'　'
			)|| '|-'
		END
	) || TEMPNAME AS DEPTREENAME
FROM
	T
ORDER BY TEMPPATH

向上递归

WITH RECURSIVE TESTTABLE (
	TEMPID,
	TEMPNAME,
	TEMPPID,
	SORTBY
) AS (
		SELECT '0' TEMPID, 'TEST1' TEMPNAME,'' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '1' TEMPID, 'TEST1-1' TEMPNAME,'0' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '2' TEMPID, 'TEST1-2' TEMPNAME,'0' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '3' TEMPID, 'TEST1-3' TEMPNAME,'0' TEMPPID, 3 SORTBY
		UNION ALL
		SELECT '11' TEMPID, 'TEST1-1-1' TEMPNAME,'1' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '12' TEMPID, 'TEST1-1-2' TEMPNAME,'1' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '13' TEMPID, 'TEST1-1-3' TEMPNAME,'1' TEMPPID, 3 SORTBY
),T AS (
	SELECT
		D1.TEMPID,
		D1.TEMPNAME,
		D1.TEMPPID,
		ARRAY [D1.SORTBY] AS TEMPPATH,
		1 AS DEPTH
	FROM
		TESTTABLE D1
	WHERE
		D1.TEMPID = '13'
	UNION ALL 
	SELECT
		D.TEMPID,
		D.TEMPNAME,
		D.TEMPPID,
		T.TEMPPATH || D.SORTBY TEMPPATH,
		T.DEPTH + 1 AS DEPTH
	FROM
		TESTTABLE D
	JOIN T ON
		D.TEMPID = T.TEMPPID
) 
SELECT TEMPID, TEMPPATH,
	(
		CASE
			WHEN DEPTH<2 THEN '<>'
			ELSE LPAD (
				' ',
				DEPTH * 1,
				'　'
			)|| '|-'
		END
	) || TEMPNAME AS DEPTREENAME
FROM
	T
ORDER BY TEMPPATH

向下递归并显示层级

WITH RECURSIVE TESTTABLE (
	TEMPID,
	TEMPNAME,
	TEMPPID,
	SORTBY
) AS (
		SELECT '0' TEMPID, 'TEST1' TEMPNAME,'' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '1' TEMPID, 'TEST1-1' TEMPNAME,'0' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '2' TEMPID, 'TEST1-2' TEMPNAME,'0' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '3' TEMPID, 'TEST1-3' TEMPNAME,'0' TEMPPID, 3 SORTBY
		UNION ALL
		SELECT '11' TEMPID, 'TEST1-1-1' TEMPNAME,'1' TEMPPID, 1 SORTBY
		UNION ALL
		SELECT '12' TEMPID, 'TEST1-1-2' TEMPNAME,'1' TEMPPID, 2 SORTBY
		UNION ALL
		SELECT '13' TEMPID, 'TEST1-1-3' TEMPNAME,'1' TEMPPID, 3 SORTBY
		UNION ALL
		SELECT '131' TEMPID, 'TEST1-1-3-1' TEMPNAME,'13' TEMPPID, 3 SORTBY
), T1(PATHID,PATHNAME,DEPTH,TEMPID,TEMPNAME,TEMPPID) AS (  
	SELECT ARRAY[TEMPID]::TEXT[] AS PATHID,ARRAY[TEMPNAME]::TEXT[] AS PATHNAME,1 AS DEPTH,
	       TEMPID,TEMPNAME,TEMPPID
	  FROM TESTTABLE
	UNION
	SELECT ARRAY[M1.TEMPID]::TEXT[]||M2.PATHID AS PATHID,ARRAY[M1.TEMPNAME]::TEXT[]||M2.PATHNAME AS PATHNAME,M2.DEPTH+1 AS DEPTH,
	       M1.TEMPID,M1.TEMPNAME,M1.TEMPPID
	  FROM TESTTABLE M1,  
		   T1 M2
	 WHERE 1=1
	   AND M1.TEMPID=M2.TEMPPID
), T2 (PATHID,PATHNAME,DEPTH,TEMPID,TEMPNAME,TEMPPID) AS (  
	SELECT ARRAY[TEMPID]::TEXT[] AS PATHID,ARRAY[TEMPNAME]::TEXT[] AS PATHNAME,1 AS DEPTH,
	       TEMPID,TEMPNAME,TEMPPID
	  FROM TESTTABLE
	UNION
	SELECT M2.PATHID||ARRAY[M1.TEMPID]::TEXT[] AS PATHID,M2.PATHNAME||ARRAY[M1.TEMPNAME]::TEXT[] AS PATHNAME,M2.DEPTH+1 AS DEPTH,
	       M1.TEMPID,M1.TEMPNAME,M1.TEMPPID
	  FROM TESTTABLE M1,  
	  	   T2 M2  
	 WHERE 1=1
	   AND M1.TEMPPID=M2.TEMPID  
), T3 AS ( 
	SELECT TEMPID,TEMPNAME,TEMPPID   
	  FROM T1
	UNION 
	SELECT TEMPID,TEMPNAME,TEMPPID  
	  FROM T2
), T4 (TEMPID,TEMPNAME,TEMPPID,PATHID,PATHNAME,DEPTH) AS (
	SELECT TEMPID, TEMPNAME, TEMPPID, ARRAY[TEMPID]::TEXT[] AS PATHID, ARRAY[TEMPNAME]::TEXT[] AS PATHNAME, 1 AS DEPTH
	  FROM T3 WHERE (TEMPPID = '' OR TEMPPID IS NULL)
	  UNION
	SELECT M1.TEMPID, M1.TEMPNAME, M1.TEMPPID, M2.PATHID||ARRAY[M1.TEMPID]::TEXT[] AS PATHID,M2.PATHNAME||ARRAY[M1.TEMPNAME]::TEXT[] AS PATHNAME,M2.DEPTH+1 AS DEPTH
	  FROM T3 M1,  
	       T4 M2
	 WHERE 1=1
	   AND M1.TEMPPID=M2.TEMPID
)
SELECT TEMPID,
	(
		CASE
			WHEN DEPTH<2 THEN '<>'
			ELSE LPAD (
				' ',
				DEPTH * 1,
				'　'
			)|| '|-'
		END
	) || TEMPNAME AS DEPTREENAME,
	'/'||ARRAY_TO_STRING(PATHID,'/') AS PATHID,
    '/'||ARRAY_TO_STRING(PATHNAME,'/') AS PATHNAME
FROM
	T4
ORDER BY DEPTH