pgsql 实现oracle的start with与层级树level

oracleSELECT LEVEL AS LVL,TPL.PORT_ID,TPL.PARENT_PORTIDFROM TN_PONLINK TPLSTART WITH TPL.DEV_ID = 'F62A1470D64111E6990CAC577F000001'CONNECT BY PRIOR

zdway

3624人浏览 · 2021-12-16 11:24:50

zdway · 2021-12-16 11:24:50 发布

oracle

   			 SELECT LEVEL AS LVL,
                          TPL.PORT_ID,
                          TPL.PARENT_PORTID
              FROM TN_PONLINK TPL
              START WITH TPL.DEV_ID = 'F62A1470D64111E6990CAC577F000001'
              CONNECT BY PRIOR TPL.PARENT_PORT_IN = TPL.PORT_ID

在这里插入图片描述
pgsql

select A.LVL,
	   A.PORT_ID,
       A.PARENT_PORTID	
from (
WITH RECURSIVE R AS (
		SELECT 1 as LVL,TPL.PORT_ID,TPL.PARENT_PORTID,PARENT_PORT_IN
			FROM (select * from TN_PONLINK
			WHERE DEV_ID = 'B24AE78015B711E5BE747DD47F000001') TPL
		UNION ALL
		SELECT R.LVL+1 as LVL,
				TPI.PORT_ID,
				TPI.PARENT_PORTID,
				TPI.PARENT_PORT_IN
			FROM TN_PONLINK TPI
			JOIN R ON R.PARENT_PORT_IN = TPI.PORT_ID
	)SELECT LVL,PORT_ID,PARENT_PORTID FROM R ) as A

在这里插入图片描述