· 4 years ago · Jun 02, 2021, 01:02 PM
1IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'menu')
2
3 DROP TABLE menu
4
5GO
6
7CREATE TABLE menu (Id TINYINT NOT NULL, IdRoot TINYINT NULL, Name VARCHAR(10) NOT NULL, Position TINYINT NOT NULL)
8
9GO
10
11INSERT INTO menu (Id, idRoot, Name, Position) VALUES
12
13 (1, 0, '0', 1),
14
15 (4, 1, '1', 1),
16
17 (2, 1, '2', 2),
18
19 (3, 2, '2.1', 1),
20
21 (6, 3, '2.1.1', 1),
22
23 (8, 6, '2.1.1.1', 1),
24
25 (5, 2, '2.2', 2),
26
27 (9, 2, '2.3', 3),
28
29 (7, 1, '3', 3)
30
31GO
32
33WITH menuCTE (Id, IdRoot, Name, Position, Ord) AS (
34
35 SELECT Id, IdRoot, Name, Position, 0 FROM menu WHERE IdRoot = 0
36
37 UNION ALL
38
39 SELECT m.Id, m.IdRoot, m.Name, m.Position, mCTE.Ord + 1 FROM menu m INNER JOIN menuCTE mCTE ON m.IdRoot = mCTE.Id
40
41)
42
43SELECT * FROM menuCTE