如何利用SQL Server With As递归获取层级关系数据

来源：互联网发布：vb.net和c#区别编辑：程序博客网时间：2024/06/08 00:04

如果已知当前用户ID我要想知道他的上级领导有哪些，可编写sql语句如下：

WITH    Emp             AS ( SELECT   ID ,                           EName ,                           ParentGUID                  FROM     dbo.Employee                  WHERE    ID = '5C8214EC-258B-4C44-9F31-206E499F0285'               UNION ALL                SELECT   d.ID ,                           d.EName ,                           d.ParentGUID                  FROM     Emp                           INNER JOIN dbo.Employee d ON d.ID = Emp.ParentGUID                )       SELECT ID,EName       FROM    Emp

相反，如果已知当前用户ID，怎么获取他的下级呢，编写sql语句如下

WITH    Emp             AS ( SELECT   ID ,                           EName ,                           ParentGUID                  FROM     dbo.Employee                  WHERE    ID = '0CD19311-2CA1-4120-9554-11BFD8219AF9'               UNION ALL                SELECT   d.ID ,                           d.EName ,                           d.ParentGUID                  FROM     Emp                           INNER JOIN dbo.Employee d ON d.ParentGUID = Emp.ID                )       SELECT ID,EName       FROM    Emp

0 0