SqlServer树形结构、层次查询

CREATE TABLE dbo.Employees ( empid INT NOT NULL PRIMARY KEY, mgrid INT NULL REFERENCES dbo.Employees, empname VARCHAR(25) NOT NULL, salary MONEY NOT NULL, CHECK (empid <> mgrid) ); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(1, NULL, 'David', $10000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(2, 1, 'Eitan', $7000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(3, 1, 'Ina', $7500.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(4, 2, 'Seraph', $5000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(5, 2, 'Jiru', $5500.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(6, 2, 'Steve', $4500.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(7, 3, 'Aaron', $5000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(8, 5, 'Lilach', $3500.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(9, 7, 'Rita', $3000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(10, 5, 'Sean', $3000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(11, 7, 'Gabriel', $3000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(12, 9, 'Emilia' , $2000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(13, 9, 'Michael', $2000.00); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES(14, 9, 'Didi', $1500.00); --创建索引 CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid); go CREATE FUNCTION dbo.fn_subordinates3 (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE ( empid INT NOT NULL PRIMARY KEY NONCLUSTERED, lvl INT NOT NULL, path VARCHAR(900) NOT NULL UNIQUE CLUSTERED(lvl, empid) ) AS BEGIN DECLARE @lvl AS INT; SET @lvl = 0; SET @maxlevels = COALESCE(@maxlevels, 2147483647); INSERT INTO @Subs(empid, lvl, path) SELECT empid, @lvl, CAST(empid AS VARCHAR(100))--ZHU YI ZHE LI FROM dbo.Employees WHERE empid = @root; WHILE @@rowcount > 0 AND @lvl < @maxlevels BEGIN SET @lvl = @lvl + 1; INSERT INTO @Subs(empid, lvl, path) SELECT C.empid, @lvl, P.path +'-'+ CAST(C.empid AS VARCHAR(100)) --和上面类型保持一致 FROM @Subs AS P JOIN dbo.Employees AS C ON P.lvl = @lvl - 1 AND C.mgrid = P.empid; END RETURN; END GO SELECT empid, path FROM dbo.fn_subordinates3(1, NULL) AS S ORDER BY PATH GO DROP TABLE Employees DROP FUNCTION dbo.fn_subordinates3