SQL Server递归实例

 

 

例子一

-- =============================================
-- 根据EID返回其下属的EID,Layer=1表示直接下属,NULL返回所有下属
-- select EID FROM F_Team_GetSubordinate(2,NULL) -- ============================================= CREATE FUNCTION F_Team_GetSubordinate( @EID INT, @Layer INT) RETURNS @tb TABLE (EID INT,NameCN varchar(50),SupervisorEID INT,Layer INT) AS BEGIN ;WITH CTE AS ( SELECT EID,NameCn,SupervisorEID,0 as Layer FROM TCFG_Employee WHERE EID=@EID UNION ALL SELECT A.EID,A.NameCn,A.SupervisorEID,Layer=layer+1 FROM TCFG_Employee A JOIN CTE B ON A.SupervisorEID = B.EID ) INSERT INTO @tb select EID,NameCN,SupervisorEID,Layer from CTE where (@Layer IS NULL OR Layer=@Layer) return END GO

例子二

--[员工]表含成层级关系
CREATE TABLE Employees (
    empid INT NOT NULL, mgrid INT NULL,--管理者ID字段,用于链接empid empname VARCHAR (25) NOT NULL, salary money NOT NULL, CONSTRAINT PK_Employees PRIMARY KEY (empid), ) GO -- 插入实例数据,允许员工的管理者ID字段为null, INSERT INTO Employees VALUES(1, NULL, 'Nancy', $10000.00) INSERT INTO Employees VALUES(2, 1, 'Andrew', $5000.00) INSERT INTO Employees VALUES(3, 1, 'Janet', $5000.00) INSERT INTO Employees VALUES(4, 1, 'Margaret',$5000.00) INSERT INTO Employees VALUES(5, 2, 'Steven', $2500.00) INSERT INTO Employees VALUES(6, 2, 'Michael', $2500.00) INSERT INTO Employees VALUES(7, 3, 'Robert', $2500.00) INSERT INTO Employees VALUES(8, 3, 'Laura', $2500.00) INSERT INTO Employees VALUES(9, 3, 'Ann', $2500.00) INSERT INTO Employees VALUES(10, 4, 'Ina', $2500.00) INSERT INTO Employees VALUES(11, 7, 'David', $2000.00) INSERT INTO Employees VALUES(12, 7, 'Ron', $2000.00) INSERT INTO Employees VALUES(13, 7, 'Dan', $2000.00) INSERT INTO Employees VALUES(14, 11, 'James', $1500.00) GO --Create Departments table and insert demo values CREATE TABLE Departments ( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR (25) NOT NULL, deptmgrid INT NULL REFERENCES Employees --部门管理者ID,外键参考Employees的empid字段 ) GO INSERT INTO Departments VALUES (1, 'HR', 2) INSERT INTO Departments VALUES (2, 'Marketing', 7) INSERT INTO Departments VALUES (3, 'Finance', 8) INSERT INTO Departments VALUES (4, 'R&D', 9) INSERT INTO Departments VALUES (5, 'Training', 4) INSERT INTO Departments VALUES (6, 'Gardening

转载于:https://www.cnblogs.com/zhuji/p/9261604.html