当前位置： 文江博客话题详情

用于显示分层数据的 SQL Server 查询或工具

发布于 2024-09-11 18:38:36 字数 2260 浏览 2 评论 0原文

我们有一个通用的组织表结构，可以将其视为树形或金字塔层次结构。我们基本上有多个想要展示的“树”。为一家公司，为另一家公司等。

有谁知道显示这些数据的好方法？ SQL 查询会很好，怀疑它是否可能，但我不会反对使用一些 OTS 工具（最好是免费的）。我还想避免某种类型的报告。我不需要实际的解决方案，只需知道是否可能。因此，如果你说 SQL，如果你能给我一个显示根离开的 2 表示例，我会很高兴。

结构非常一般

alt text

每个表都通过代理链接key CompanyID、CompanyGroupID 等。

对于我们显示/查询这些数据的方式有什么建议吗？最后的手段是编写一个快速的 C# Windows 应用程序...

我们希望以树的形式看到它：

--                      1-Company
--                     /        \
--             CompanyGroupA   CompanyGroupB
--            /       \              \
--  CompanyStoreA1 CompanyStoreA1 CompanyStoreB
--    /      \            /    \
--Employee   A            B     C

为了取悦大众，这里有一个用于填充查询的示例测试脚本。

DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  


DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets',1 ) 
INSERT @CompanyGroup VALUES (2,'Humans',1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics',2 ) 
INSERT @CompanyGroup VALUES (4,'Food',2 ) 


DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA',1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB',1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC',1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD', 1) 
INSERT @CompanyStore VALUES (5,'HumansStore',2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore',3 )

最终的解决方案非常棒，我修改了 usp_DrawTree 以接受 varchar 与 ints，因为我必须使我的查询 id 唯一。然后我只是做了一个选择/联合所有并建立了父子关系。

select * into #TreeData from (
  select ID='C' + cast(id as varchar(10)),
       ParentID=null,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from Company c 
 )
union all (
  select ID='CG' + cast(id as varchar(10)),
       ParentID=cg.CompanyID ,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from CompanyGroup cg join Company c on c.ID=cg.CompanyID 
  ) 
//union all rest of hierarchy
)
原文

We have a general organizational table structure, think of it s a Tree or Pyramid Hierarchy. We basically have multiple "trees" we want to show. On for one company, one for another ETC.

Does anyone know of a good way to display this data? SQL Query would be nice, doubt it would be possible, but I wouldn't be against using some OTS tool (preferably free). I would also like to avoid some type of report. I don't need an actual solution just need to know if its possible. So if you say SQL if you can give me a 2 table example of showing a root a leave I would be happy.

Structure is pretty general

alt text

And each table is linked through a surrogate key CompanyID, CompanyGroupID, etc.

Any suggestions on ways we could display/query for this data? Last resort is to write a quick C# Windows Application...

We would like to see it in tree form:

--                      1-Company
--                     /        \
--             CompanyGroupA   CompanyGroupB
--            /       \              \
--  CompanyStoreA1 CompanyStoreA1 CompanyStoreB
--    /      \            /    \
--Employee   A            B     C

In attempt to please the masses here is an example test script to populate the query.

DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  


DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets',1 ) 
INSERT @CompanyGroup VALUES (2,'Humans',1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics',2 ) 
INSERT @CompanyGroup VALUES (4,'Food',2 ) 


DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA',1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB',1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC',1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD', 1) 
INSERT @CompanyStore VALUES (5,'HumansStore',2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore',3 )

The final solution was pretty awesome I modified the usp_DrawTree to accept varchar vs ints because i had to make my query ids unique. I then just did a select/union all and built the parent child relationship.

select * into #TreeData from (
  select ID='C' + cast(id as varchar(10)),
       ParentID=null,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from Company c 
 )
union all (
  select ID='CG' + cast(id as varchar(10)),
       ParentID=cg.CompanyID ,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from CompanyGroup cg join Company c on c.ID=cg.CompanyID 
  ) 
//union all rest of hierarchy
)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

鱼窥荷 2024-09-18 18:38:36

Brad Schulz 以 usp_DrawTree。

替代文本

Brad Schulz to the rescue in the form of usp_DrawTree.

alt text

回复 原文
人间☆小暴躁 2024-09-18 18:38:36

您不提供任何表结构，因此这里是处理树结构的递归 CTE 的示例：

--go through a nested table supervisor - user table and display the chain
DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /        \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /       \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /      \            /    \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  char(4)

--get complete tree---------------------------------------------------
SET @Root_id=null
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree


--get all below 2---------------------------------------------------
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

--get all below 6---------------------------------------------------
SET @Root_id=6
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

OUTPUT:

@Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
9      Bill       1             1          Jerome             2
10     Sam        9             9          Bill               3
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(10 row(s) affected)

@Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

@Root_id='6   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
6      David      2             2          Joe                1
7      Ian        6             6          David              2
8      Helen      6             6          David              2

(3 row(s) affected)

EDIT based on OP's给定的表和数据：

尝试如下所示：

SET NOCOUNT ON
DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  

DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets'        ,1 ) 
INSERT @CompanyGroup VALUES (2,'Humans'      ,1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics' ,2 ) 
INSERT @CompanyGroup VALUES (4,'Food'        ,2 ) 

DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA'   ,1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB'   ,1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC'   ,1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD'   ,1) 
INSERT @CompanyStore VALUES (5,'HumansStore'  ,2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore'    ,3 ) 

--not provided by the OP, so I made it up
DECLARE @CompanyEmployees table (id int, name varchar(10), reports_to_id int, CompanyStoreID int)
INSERT @CompanyEmployees VALUES (1,'Jerome', NULL ,1)  -- tree is as follows:
INSERT @CompanyEmployees VALUES (2,'Joe'   ,1     ,1)     --                      PetsStoreA             PetsStoreB         PetStoreC          FoodStore
INSERT @CompanyEmployees VALUES (3,'Paul'  ,2     ,1)     --                      1-Jerome                 11-Alan           14-Ben              18-apple
INSERT @CompanyEmployees VALUES (4,'Jack'  ,3     ,1)     --                     /        \                /      \           /                  /     \
INSERT @CompanyEmployees VALUES (5,'Daniel',3     ,1)     --              2-Joe           9-Bill         12-Ally  13-Abby    15-Bill         19-pear   20-grape
INSERT @CompanyEmployees VALUES (6,'David' ,2     ,1)     --            /       \              \                             /      \                    /
INSERT @CompanyEmployees VALUES (7,'Ian'   ,6     ,1)     --     3-Paul          6-David       10-Sam                     16-Bjorn  17-Benny           21-rasin
INSERT @CompanyEmployees VALUES (8,'Helen' ,6     ,1)     --    /      \            /    \
INSERT @CompanyEmployees VALUES (9,'Bill ' ,1     ,1)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @CompanyEmployees VALUES (10,'Sam'  ,9     ,1)     --
INSERT @CompanyEmployees VALUES (11,'Alan' ,NULL  ,2)     --to see all trees, scroll--->>
INSERT @CompanyEmployees VALUES (12,'Ally' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (13,'Abby' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (14,'Ben'  ,NULL  ,3)     --     
INSERT @CompanyEmployees VALUES (15,'Bill' ,14    ,3)     --
INSERT @CompanyEmployees VALUES (16,'Bjorn',15    ,3)     --
INSERT @CompanyEmployees VALUES (17,'Benny',15    ,3)     --
INSERT @CompanyEmployees VALUES (18,'apple',NULL  ,6)     --
INSERT @CompanyEmployees VALUES (19,'pear' ,18    ,6)     --
INSERT @CompanyEmployees VALUES (20,'grape',18    ,6)     --
INSERT @CompanyEmployees VALUES (21,'rasin',21    ,6)     --
SET NOCOUNT OFF

;WITH StaffTree AS
(
    SELECT 
        c.id, c.name, c.reports_to_id, c.reports_to_id as Manager_id, cc.name AS Manager_name, 1 AS LevelOf, c.CompanyStoreID
        FROM @CompanyEmployees                c
            LEFT OUTER JOIN @CompanyEmployees cc ON c.reports_to_id=cc.id
        WHERE c.reports_to_id IS NULL
    UNION ALL
        SELECT 
            s.id, s.name, s.reports_to_id, t.id, t.name, t.LevelOf+1, s.CompanyStoreID
        FROM StaffTree                    t
            INNER JOIN @CompanyEmployees  s ON t.id=s.reports_to_id
)
SELECT
    c.id AS CompanyID, c.name AS CompanyName
        ,g.id AS CompanyGroupID, g.name AS CompanyName
        ,s.id AS CompanyStoreID, s.name AS CompanyStoreName
        ,t.id AS EmployeeID, t.name as EmployeeName, t.Manager_id, t.Manager_name, t.LevelOf
    FROM @Company                c
        LEFT JOIN @CompanyGroup  g ON c.id=g.CompanyID
        LEFT JOIN @CompanyStore  s ON g.id=s.CompanyGroupID
        LEFT JOIN StaffTree      t ON s.id=t.CompanyStoreID
    ORDER BY c.name,g.name,s.name,s.ID,t.LevelOf,t.name

OUTPUT:

CompanyID CompanyName    CompanyGroupID CompanyName CompanyStoreID CompanyStoreName EmployeeID  EmployeeName Manager_id  Manager_name LevelOf
--------- -------------- -------------- ----------- -------------- ---------------- ----------- ------------ ----------- ------------ -------
2         Boring Company 3              Electronics 6              FoodStore        18          apple        NULL        NULL         1
2         Boring Company 3              Electronics 6              FoodStore        20          grape        18          apple        2
2         Boring Company 3              Electronics 6              FoodStore        19          pear         18          apple        2
2         Boring Company 4              Food        NULL           NULL             NULL        NULL         NULL        NULL         NULL
1         Living Things  2              Humans      5              HumansStore      NULL        NULL         NULL        NULL         NULL
1         Living Things  1              Pets        1              PetsStoreA       1           Jerome       NULL        NULL         1
1         Living Things  1              Pets        1              PetsStoreA       9           Bill         1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       2           Joe          1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       6           David        2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       3           Paul         2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       10          Sam          9           Bill         3
1         Living Things  1              Pets        1              PetsStoreA       5           Daniel       3           Paul         4
1         Living Things  1              Pets        1              PetsStoreA       8           Helen        6           David        4
1         Living Things  1              Pets        1              PetsStoreA       7           Ian          6           David        4
1         Living Things  1              Pets        1              PetsStoreA       4           Jack         3           Paul         4
1         Living Things  1              Pets        2              PetsStoreB       11          Alan         NULL        NULL         1
1         Living Things  1              Pets        2              PetsStoreB       13          Abby         11          Alan         2
1         Living Things  1              Pets        2              PetsStoreB       12          Ally         11          Alan         2
1         Living Things  1              Pets        3              PetsStoreC       14          Ben          NULL        NULL         1
1         Living Things  1              Pets        3              PetsStoreC       15          Bill         14          Ben          2
1         Living Things  1              Pets        3              PetsStoreC       17          Benny        15          Bill         3
1         Living Things  1              Pets        3              PetsStoreC       16          Bjorn        15          Bill         3
1         Living Things  1              Pets        4              PetsStoreD       NULL        NULL         NULL        NULL         NULL

(23 row(s) affected)

编辑在OP编辑后指出我们希望以树形形式看到它。

该问题被标记为sql-server-2008和hierarchical-data，并且OP想要进行复杂的格式化以显示数据。然而，这种类型的处理和显示不是 TSQL 的领域，而是应用程序语言应在何处处理和格式化 SQL 查询提供的平面数据的一个非常清晰的示例。我提供了这样一个查询，应用程序可以使用它来构建可视化树显示。另请注意，简单的树示例（每个父级不超过两个子级）可能不太现实，并且当单个父级存在许多子级时，显示将变得难以构建并且不美观。

you don't provide any table structure, so here is a sample of a recursive CTE processing a tree structure:

--go through a nested table supervisor - user table and display the chain
DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /        \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /       \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /      \            /    \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  char(4)

--get complete tree---------------------------------------------------
SET @Root_id=null
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree


--get all below 2---------------------------------------------------
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

--get all below 6---------------------------------------------------
SET @Root_id=6
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

OUTPUT:

@Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
9      Bill       1             1          Jerome             2
10     Sam        9             9          Bill               3
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(10 row(s) affected)

@Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

@Root_id='6   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
6      David      2             2          Joe                1
7      Ian        6             6          David              2
8      Helen      6             6          David              2

(3 row(s) affected)

EDIT based on OP's given tables and data:

try something like this:

SET NOCOUNT ON
DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  

DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets'        ,1 ) 
INSERT @CompanyGroup VALUES (2,'Humans'      ,1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics' ,2 ) 
INSERT @CompanyGroup VALUES (4,'Food'        ,2 ) 

DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA'   ,1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB'   ,1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC'   ,1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD'   ,1) 
INSERT @CompanyStore VALUES (5,'HumansStore'  ,2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore'    ,3 ) 

--not provided by the OP, so I made it up
DECLARE @CompanyEmployees table (id int, name varchar(10), reports_to_id int, CompanyStoreID int)
INSERT @CompanyEmployees VALUES (1,'Jerome', NULL ,1)  -- tree is as follows:
INSERT @CompanyEmployees VALUES (2,'Joe'   ,1     ,1)     --                      PetsStoreA             PetsStoreB         PetStoreC          FoodStore
INSERT @CompanyEmployees VALUES (3,'Paul'  ,2     ,1)     --                      1-Jerome                 11-Alan           14-Ben              18-apple
INSERT @CompanyEmployees VALUES (4,'Jack'  ,3     ,1)     --                     /        \                /      \           /                  /     \
INSERT @CompanyEmployees VALUES (5,'Daniel',3     ,1)     --              2-Joe           9-Bill         12-Ally  13-Abby    15-Bill         19-pear   20-grape
INSERT @CompanyEmployees VALUES (6,'David' ,2     ,1)     --            /       \              \                             /      \                    /
INSERT @CompanyEmployees VALUES (7,'Ian'   ,6     ,1)     --     3-Paul          6-David       10-Sam                     16-Bjorn  17-Benny           21-rasin
INSERT @CompanyEmployees VALUES (8,'Helen' ,6     ,1)     --    /      \            /    \
INSERT @CompanyEmployees VALUES (9,'Bill ' ,1     ,1)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @CompanyEmployees VALUES (10,'Sam'  ,9     ,1)     --
INSERT @CompanyEmployees VALUES (11,'Alan' ,NULL  ,2)     --to see all trees, scroll--->>
INSERT @CompanyEmployees VALUES (12,'Ally' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (13,'Abby' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (14,'Ben'  ,NULL  ,3)     --     
INSERT @CompanyEmployees VALUES (15,'Bill' ,14    ,3)     --
INSERT @CompanyEmployees VALUES (16,'Bjorn',15    ,3)     --
INSERT @CompanyEmployees VALUES (17,'Benny',15    ,3)     --
INSERT @CompanyEmployees VALUES (18,'apple',NULL  ,6)     --
INSERT @CompanyEmployees VALUES (19,'pear' ,18    ,6)     --
INSERT @CompanyEmployees VALUES (20,'grape',18    ,6)     --
INSERT @CompanyEmployees VALUES (21,'rasin',21    ,6)     --
SET NOCOUNT OFF

;WITH StaffTree AS
(
    SELECT 
        c.id, c.name, c.reports_to_id, c.reports_to_id as Manager_id, cc.name AS Manager_name, 1 AS LevelOf, c.CompanyStoreID
        FROM @CompanyEmployees                c
            LEFT OUTER JOIN @CompanyEmployees cc ON c.reports_to_id=cc.id
        WHERE c.reports_to_id IS NULL
    UNION ALL
        SELECT 
            s.id, s.name, s.reports_to_id, t.id, t.name, t.LevelOf+1, s.CompanyStoreID
        FROM StaffTree                    t
            INNER JOIN @CompanyEmployees  s ON t.id=s.reports_to_id
)
SELECT
    c.id AS CompanyID, c.name AS CompanyName
        ,g.id AS CompanyGroupID, g.name AS CompanyName
        ,s.id AS CompanyStoreID, s.name AS CompanyStoreName
        ,t.id AS EmployeeID, t.name as EmployeeName, t.Manager_id, t.Manager_name, t.LevelOf
    FROM @Company                c
        LEFT JOIN @CompanyGroup  g ON c.id=g.CompanyID
        LEFT JOIN @CompanyStore  s ON g.id=s.CompanyGroupID
        LEFT JOIN StaffTree      t ON s.id=t.CompanyStoreID
    ORDER BY c.name,g.name,s.name,s.ID,t.LevelOf,t.name

OUTPUT:

CompanyID CompanyName    CompanyGroupID CompanyName CompanyStoreID CompanyStoreName EmployeeID  EmployeeName Manager_id  Manager_name LevelOf
--------- -------------- -------------- ----------- -------------- ---------------- ----------- ------------ ----------- ------------ -------
2         Boring Company 3              Electronics 6              FoodStore        18          apple        NULL        NULL         1
2         Boring Company 3              Electronics 6              FoodStore        20          grape        18          apple        2
2         Boring Company 3              Electronics 6              FoodStore        19          pear         18          apple        2
2         Boring Company 4              Food        NULL           NULL             NULL        NULL         NULL        NULL         NULL
1         Living Things  2              Humans      5              HumansStore      NULL        NULL         NULL        NULL         NULL
1         Living Things  1              Pets        1              PetsStoreA       1           Jerome       NULL        NULL         1
1         Living Things  1              Pets        1              PetsStoreA       9           Bill         1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       2           Joe          1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       6           David        2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       3           Paul         2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       10          Sam          9           Bill         3
1         Living Things  1              Pets        1              PetsStoreA       5           Daniel       3           Paul         4
1         Living Things  1              Pets        1              PetsStoreA       8           Helen        6           David        4
1         Living Things  1              Pets        1              PetsStoreA       7           Ian          6           David        4
1         Living Things  1              Pets        1              PetsStoreA       4           Jack         3           Paul         4
1         Living Things  1              Pets        2              PetsStoreB       11          Alan         NULL        NULL         1
1         Living Things  1              Pets        2              PetsStoreB       13          Abby         11          Alan         2
1         Living Things  1              Pets        2              PetsStoreB       12          Ally         11          Alan         2
1         Living Things  1              Pets        3              PetsStoreC       14          Ben          NULL        NULL         1
1         Living Things  1              Pets        3              PetsStoreC       15          Bill         14          Ben          2
1         Living Things  1              Pets        3              PetsStoreC       17          Benny        15          Bill         3
1         Living Things  1              Pets        3              PetsStoreC       16          Bjorn        15          Bill         3
1         Living Things  1              Pets        4              PetsStoreD       NULL        NULL         NULL        NULL         NULL

(23 row(s) affected)

EDIT after OP's edit stating that We would like to see it in tree form.

The question is tagged sql-server-2008 and hierarchical-data, and the OP wants to do complex formatting for display f the data. However this type of processing and display is not the realm of TSQL and is a very clear example of where the application language should process and format flat data provided by a SQL query. I have provided such a query that could be used by an application to build a visual tree display. Also note that the simple tree example (no more than two children per parent) might not be very realistic and when many children exist for a single parent, the display will become difficult to construct and not pleasing to the eye.

回复 原文
￡烟消云散 2024-09-18 18:38:36

您可以使用报告服务来显示它，就像通过 SQL 2008 获得的那样；如果你幸运的话，它可能已经设置好了——如果不是的话，那也很容易做到。您可以使用报告服务中的钻取功能来允许您的用户根据需要轻松钻取和钻出数据。

在查询方面；这棵树是生长还是固定的？从数据库中获取数据的 SQL 查询非常简单。

Select 
    CompanyName,
    CompanyGroupName,
    CompanyStoreName,
    CompanyEmployeeForename,
    CompanyEmployeeSurname

From tblCompanies com
left outer join tblCompanyGroups cg
on com.CompanyGroupID = cg.CompanyGroupID

Left outer Join tblCompanyStore cs
on com.CompanyID = cs.CompanyID

left outer join tblCompanyEmployees ce
on com.CompanyID = ce.CompanyName

You could use reporting services to display it back which you get with SQL 2008; if you are lucky it might be setup already -- if not its quite easy to do that. You could use the drill in features in reporting services to get allow your users to drill in and out of the data as needed very easily.

In terms of the query; does the tree grow or is it fixed? The SQL Query to get the data out from the database is quite simple though.

Select 
    CompanyName,
    CompanyGroupName,
    CompanyStoreName,
    CompanyEmployeeForename,
    CompanyEmployeeSurname

From tblCompanies com
left outer join tblCompanyGroups cg
on com.CompanyGroupID = cg.CompanyGroupID

Left outer Join tblCompanyStore cs
on com.CompanyID = cs.CompanyID

left outer join tblCompanyEmployees ce
on com.CompanyID = ce.CompanyName
回复 原文
你的背包 2024-09-18 18:38:36

我相信 SQL Server 2008 提供了一种新的数据类型来帮助解决这种情况。这是一个我相信会有帮助的链接 - http://msdn.microsoft。 com/en-us/magazine/cc794278.aspx。我没有在任何评论中看到它，所以希望这会有所帮助。

I believe SQL Server 2008 offers a new data type to help with this scenario. Here is a link that I believe will help - http://msdn.microsoft.com/en-us/magazine/cc794278.aspx. I didn't see it in any comments, so hope this helps.

回复 原文
~没有更多了~

关于作者

佼人

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lioqio

文章 0 评论 0

Single

文章 0 评论 0

禾厶谷欠

文章 0 评论 0

alipaysp_2zg8elfGgC

文章 0 评论 0

qq_N6d4X7

文章 0 评论 0

放低过去

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文