递归查询

WITH RECURSIVE tmp0 AS (
	SELECT
		t3.ID bomid,
		t1._masterid masterid 
	FROM
		pdm_bom t3
		LEFT JOIN pdm_partmaster t2 ON t3._childpartmasterid = t2.
		ID LEFT JOIN pdm_part t1 ON t1._masterid = t2.ID 
	WHERE
		t1.latestiteration = 1 
		AND t1.latestversion = 1 
		AND t1.workingcopy = 0 
		AND t3._parentpartid = 356821641772929024 
		AND t3._tenantId = 267303003539337216 UNION
	SELECT
		t3.ID,
		tempsub._substitutepartmasterid 
	FROM
		pdm_bom t3
		LEFT JOIN pdm_partmaster t2 ON t3._childpartmasterid = t2.
		ID LEFT JOIN pdm_part t1 ON t1._masterid = t2.
		ID INNER JOIN pdm_bomsubstitutelink tempsub ON t3.ID = tempsub._bomid 
	WHERE
		t1.latestiteration = 1 
		AND t1.latestversion = 1 
		AND t1.workingcopy = 0 
		AND t3._parentpartid = 356821641772929024 
		AND t3._tenantId = 267303003539337216 
	),
	ttt AS (
	SELECT ID
		,
		_masterid,
		modifier,
	CASE
			
			WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 2 
			AND workingcopy = 0 THEN
				LEAD ( couserid ) OVER ( PARTITION BY _masterid, VERSION, iteration ORDER BY workingcopy ) ELSE couserid 
			END realcouserid,
CASE
	
	WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 2 THEN
	1 ELSE 0 
	END realworkingcopy,
CASE
	
	WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 1 THEN
TRUE ELSE
CASE
		
		WHEN workingcopy = 1 THEN
		couserid = 'test1 est1' 
		WHEN workingcopy = 0 THEN
		LEAD ( couserid ) OVER ( PARTITION BY _masterid, VERSION, iteration ORDER BY workingcopy ) <> 'test1 est1' 
	END 
	END realvisible,
	( SELECT partnumber FROM pdm_partmaster WHERE ID = _masterid ) AS partnumber,
	iteration,
	VERSION,
	description,
	descriptionen 
FROM
	pdm_part 
WHERE
	_masterid IN ( SELECT masterid FROM tmp0 ) 
	AND latestiteration = 1 
	AND latestversion = 1 
	),
	partTree AS (
	SELECT
		ttt._masterid masterid,
		ttt.realworkingcopy workingcopy,
		ttt.modifier,
		ttt.realcouserid couserid,
		ttt.ID,
		ttt.partnumber,
		ttt.iteration,
		ttt.VERSION,
		ttt.descriptionen,
		ttt.description,
		t3.ID bomid,
		t3._parentpartid AS parentpartid,
		ARRAY [ 356821641772929024,
		ttt.ID ] AS PATH,
		ARRAY [ 356821641772929024 ] AS parentpath,
		( t2.partnumber || ',' || ttt.partnumber ) AS NPATH,
		ARRAY [ t2.ID,
		ttt._masterid ] AS mpath,
		ARRAY [
	CASE
			
			WHEN t3.bomsubstitutelinkid IS NULL THEN
			t3.ID ELSE t3.bomsubstitutelinkid 
		END ] AS BOMPATH,
	1 AS LEVEL,
	t3.sequencenumber,
	ARRAY [ t3.sequencenumber ] AS sortPath,
CASE
		
		WHEN ttt.partnumber LIKE'T%' THEN
		1 
	END AS searched,
	0 AS
	repeat
		,
		t3.mainpart,
		t3.bomsubstitutelinkid 
	FROM
		(
		SELECT ID
			,
			sequencenumber,
			_tenantid,
			_parentpartid,
			_childpartmasterid,
			1 AS mainpart,
			NULL AS bomsubstitutelinkid 
		FROM
			pdm_bom UNION
		SELECT
			aaaa.ID,
			sequencenumber,
			aaaa._tenantid,
			aaaa._parentpartid,
			tempsub._substitutepartmasterid,
			0,
			tempsub.ID 
		FROM
			pdm_bom aaaa,
			pdm_bomsubstitutelink tempsub 
		WHERE
			aaaa.ID = tempsub._bomid 
		) t3
		LEFT JOIN pdm_part t1 ON t3._parentpartid = t1.
		ID LEFT JOIN pdm_partmaster t2 ON t1._masterid = t2.
		ID INNER JOIN ttt ON t3._childpartmasterid = ttt._masterid 
	WHERE
		t3._parentpartid = 356821641772929024 
		AND ttt.realvisible = TRUE 
		AND ( t3.mainpart = 0 AND t3.ID IN ( 356821642863448064 ) OR t3.mainpart = 1 ) 
		AND t3._tenantId = 267303003539337216 UNION
		(
			WITH childrenData1 AS (
			SELECT
				t3.ID AS bomid,
				t3._parentpartid AS parentpartid,
				t3._childpartmasterid AS bomchildpartmasterid,
				t3.sequencenumber,
				t0.PATH,
				t0.parentpath,
				t0.NPATH,
				t0.mpath,
				t0.BOMPATH,
				t0.sortPath,
				t0.LEVEL + 1 AS LEVEL 
			FROM
				partTree t0
				INNER JOIN pdm_bom t3 ON t3._parentpartid = t0.ID 
			),
			childrenData AS (
			SELECT
				t0.bomid,
				t0.parentpartid,
				t0.bomchildpartmasterid,
				t0.sequencenumber,
				t0.PATH,
				t0.parentpath,
				t0.NPATH,
				t0.mpath,
				t0.BOMPATH,
				t0.sortPath,
				t0.LEVEL,
				1 AS mainpart,
				NULL AS bomsubstitutelinkid 
			FROM
				childrenData1 t0 UNION
			SELECT
				t0.bomid,
				t0.parentpartid,
				tempsub._substitutepartmasterid AS bomchildpartmasterid,
				t0.sequencenumber,
				t0.PATH,
				t0.parentpath,
				t0.NPATH,
				t0.mpath,
				t0.BOMPATH,
				t0.sortPath,
				t0.LEVEL,
				0,
				tempsub.ID 
			FROM
				childrenData1 t0
				INNER JOIN pdm_bomsubstitutelink tempsub ON t0.bomid = tempsub._bomid 
			),
			ttt AS (
			SELECT ID
				,
				_masterid,
				modifier,
			CASE
					
					WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 2 
					AND workingcopy = 0 THEN
						LEAD ( couserid ) OVER ( PARTITION BY _masterid, VERSION, iteration ORDER BY workingcopy ) ELSE couserid 
					END realcouserid,
CASE
	
	WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 2 THEN
	1 ELSE 0 
	END realworkingcopy,
CASE
	
	WHEN COUNT ( 1 ) OVER ( PARTITION BY _masterid, VERSION, iteration ) = 1 THEN
TRUE ELSE
CASE
		
		WHEN workingcopy = 1 THEN
		couserid = 'test1 est1' 
		WHEN workingcopy = 0 THEN
		LEAD ( couserid ) OVER ( PARTITION BY _masterid, VERSION, iteration ORDER BY workingcopy ) <> 'test1 est1' 
	END 
	END realvisible,
	( SELECT partnumber FROM pdm_partmaster WHERE ID = _masterid ) AS partnumber,
	iteration,
	VERSION,
	description,
	descriptionen 
FROM
	pdm_part 
WHERE
	_masterid IN ( SELECT _masterid FROM childrenData ) 
	AND latestiteration = 1 
	AND latestversion = 1 
	) SELECT
	ttt._masterid masterid,
	ttt.realworkingcopy workingcopy,
	ttt.modifier,
	ttt.realcouserid couserid,
	ttt.ID,
	ttt.partnumber,
	ttt.iteration,
	ttt.VERSION,
	ttt.descriptionen,
	ttt.description,
	tt0.bomid,
	tt0.parentpartid,
	tt0.PATH || ttt.ID AS PATH,
	tt0.PATH,
	( tt0.NPATH || ',' || ttt.partnumber ) AS NPATH,
	tt0.MPATH || ttt._masterid AS MPATH,
	tt0.BOMPATH ||
CASE
		
		WHEN tt0.bomsubstitutelinkid IS NULL THEN
		tt0.bomid ELSE tt0.bomsubstitutelinkid 
	END AS BOMPATH,
	tt0.LEVEL AS LEVEL,
	tt0.sequencenumber,
	tt0.sortPath || tt0.sequencenumber AS sortPath,
CASE
		
		WHEN ttt.partnumber LIKE'T%' THEN
		1 
	END AS searched,
CASE
		
		WHEN ttt._masterid = ANY ( tt0.MPATH ) THEN
		1 ELSE 0 
END AS
	repeat
		,
		tt0.mainpart,
		tt0.bomsubstitutelinkid 
	FROM
		childrenData tt0
		INNER JOIN ttt ON tt0.bomchildpartmasterid = ttt._masterid 
	WHERE
		ARRAY_LENGTH( ARRAY ( SELECT DISTINCT cid FROM UNNEST ( tt0.PATH ) AS cid ), 1 ) - ARRAY_LENGTH( tt0.PATH, 1 ) = 0 
		AND ttt.realvisible = TRUE 
		AND ( tt0.mainpart = 0 AND tt0.bomid IN ( 356821642863448064 ) OR tt0.mainpart = 1 ) 
	) 
	) SELECT T
	.sequencenumber,
	array_to_string( T.sortpath, ',' ) AS sortpath,
	T.searched,
	T.
	repeat
		,
	CASE
			
			WHEN NOT EXISTS ( SELECT 1 FROM parttree tttt WHERE T.ID = tttt.parentpartid ) THEN
			1 ELSE 0 
		END AS leafNodeFlag,
		T.workingcopy workingcopy,
		T.couserid couserid,
		T.modifier modifier,
		T.modifier checkoutuser,
		T.partnumber childPartNumber,
		T.VERSION partversion,
		T.iteration partiteration,
		T.description descriptionCn,
		T.ID childPartId,
		T.LEVEL AS LEVEL,
		T.NPATH AS partnumberpath,
		array_to_string( T.PATH, ',' ) AS PATH,
		array_to_string( T.parentPATH, ',' ) AS parentPATH,
		array_to_string( T.BOMPATH, ',' ) AS treepath,
		array_to_string( T.mpath, ',' ) AS masterpath,
		T.bomid,
		T.parentpartid,
		T.masterid,
		T.mainpart substitute,
		T.bomsubstitutelinkid 
	FROM
		parttree T 
	WHERE
		EXISTS (
		SELECT
			1 
		FROM
			(
			SELECT
				tt.parentpath 
			FROM
				parttree tt 
			WHERE
				tt.partnumber LIKE'T%' 
				AND array_to_string( tt.PATH, '/' ) LIKE array_to_string( ARRAY [ 356821641772929024 ], '/' ) || '%' 
			) bb 
		WHERE
			array_to_string( bb.parentpath, '/' ) LIKE array_to_string( T.parentpath, '/' ) || '%' 
			AND array_to_string( T.parentpath, '/' ) LIKE array_to_string( ARRAY [ 356821641772929024 ], '/' ) || '%' 
		) 
		OR LEVEL = 1 
	ORDER BY
		T.sortpath,
	T.bomid,
	T.mainpart DESC