前言:開發常用的關系型資料庫mysql,mssql,postgre,oracle,簡單的增删改查的SQL語句都與标準SQL相容,這個不用講,那麼對于疊代查詢(不嚴格的叫法:遞歸查詢)每種資料庫都不一樣,對于标準SQL語句支援最好的是mssql和postgre,這個不需多講,我們隻講講單表情況下的postgre如何通過疊代查詢擷取有層級關系的資料。
一、表結構舉例
MENU表
ID VARCHAR2(32) N sys_guid() 節點id
FENXID VARCHAR2(32) Y 分項id
FENXMC VARCHAR2(100) Y 分項名稱
FUJID VARCHAR2(32) Y 父級id
FUJMC VARCHAR2(100) Y 父級名稱
二、關聯關系
子級菜單的FUJID等于父級菜單的FENXID,根節點沒有FUJID,每個節點都有一個節點ID用于友善單個節點增删改查
即((根節點,根節點無FUJID)ID,FENXID-->FUJID,FENXID(具有子級的父節點,該FUJD等于上一級的FENXID)-->FUJID,FENXID-->FUJID,FENXID-->FUJID,FENXID....以此類推)
三、疊代查詢
(1)不限層級查詢
WITH RECURSIVE locs(ID,FENXID,FENXMC,FUJID,FUJMC)
AS
(
SELECT a.ID,a.FENXID,a.FENXMC,a.FUJID,a.FUJMC,0 AS loclevel FROM menu a
where a.id='12345678000000000000'
UNION ALL
SELECT a.ID,a.FENXID,a.FENXMC,a.FUJID,a.FUJMC,loclevel+1 FROM menu a
INNER JOIN locs p ON a.fujid=p.fenxid
)
SELECT * FROM locs
查詢結果的每條資料會帶一個loclevel字段,該字段表示層級
(2)限制層級數量查詢
比如,我們隻需要查詢到第三級菜單,在上面的SQL語句中增加一個loclevel<3的條件:
SELECT a.ID,a.FENXID,a.FENXMC,a.FUJID,a.FUJMC,loclevel+1 FROM menu a
INNER JOIN locs p ON a.fujid=p.fenxid and loclevel<3