SQL CTE WITH AS 用法

CREATE TABLE Menu
(
    Id INT IDENTITY(1,1),
    Parent INT NOT NULL,
    [Name] VARCHAR(20) NOT null
)
GO
INSERT INTO Menu(Parent,[Name]) VALUES(0,'字典')
INSERT INTO Menu(Parent,[Name]) VALUES(1,'人')
INSERT INTO Menu(Parent,[Name]) VALUES(2,'男人')
INSERT INTO Menu(Parent,[Name]) VALUES(2,'女人')

--简化嵌套
WITH people AS 
(
    SELECT * FROM Menu m
)
SELECT * FROM people
go
--递归查询 父子类关系
WITH people AS 
(
    SELECT m.Id,m.Parent, m.[Name] FROM Menu m WHERE m.Id = 2
    UNION ALL
    SELECT m.Id, m.Parent, m.[Name] FROM Menu m INNER JOIN people p ON m.Parent = p.id
)
SELECT * FROM people
go