(一)菜单表[A03_MENU]结构如下:
CREATE TABLE [dbo].[A03_MENU](
[id] [INT] NOT NULL,[name] [NVARCHAR](128) NOT NULL,
[sort] [INT] NOT NULL,
[pid] [INT] NOT NULL
) ON [PRIMARY]
sort列即每一节按sort值排序。
数据结构如图所示
(二)如果查询节点id=1 即语文下的课程则SQL语句如下:
WITH cte
AS ( SELECT *
FROM dbo.T_Menu
WHERE id = '1'
UNION ALL
SELECT T_Menu.*
FROM dbo.T_Menu ,
cte
WHERE T_Menu.pid = cte.id
)
SELECT *
FROM cte
ORDER BY id,sort
结果如下:
(三)如果查询节点id=8的上级,SQL语句如下:
WITH cte
AS ( SELECT *
FROM dbo.T_Menu
WHERE id = '8'
UNION ALL
SELECT T_Menu.*
FROM dbo.T_Menu ,
cte
WHERE T_Menu.id = cte.pid
AND T_Menu.id <> cte.id
)
SELECT *
FROM cte
ORDER BY id,sort