logo标签列表

显示组织结构图的T-SQL很棘手（层次结构/递归）。

sql-serversql-server-2005hierarchical-data
3
请不要给我指向如何创建树结构或SQL中的CTE的文章，我已经读了很多！！！ 对于内心深处的t-sql来说，这可能并不那么困难，但对我来说绝对是困难的：）。
这里是情况，我必须创建一个报告，看起来像这样： alt text http://img85.imageshack.us/img85/6372/70337249.png 当我的存储过程（SQL Server sproc）的参数设置为“全部”时，这非常有效，因为它只获取所有数据，最终用户可以展开/折叠项目以查看层次结构。 当我运行报告并选择一个名称（例如在此处的“Kevin Bicking”）时，问题就会发生，请参见结果： alt text http://img69.imageshack.us/img69/8398/46964880.png 这个问题在于我只得到了Kevin的直接报告，但实际上我需要看到所有的下属。例如，在第一张图片中，我希望我的报告显示Kevin以下的所有人，以及Kelvin和Tim以下的所有人等等。
我理解这个问题，但不知道如何在T-SQL中处理它。这是我的存储过程：
CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
  SET NOCOUNT ON;    

 SELECT 
  c1.id AS EmployeeID,
  c2.id as ManagerID, 
  c1.first_name + ' ' + c1.last_name AS [EmployeeName], 
  c1.title AS Title, 
  c2.first_name + ' ' + c2.last_name AS [ReportsTo]
FROM 
  Contacts c1
INNER JOIN 
  Contacts c2
ON 
  c1.reports_to_id = c2.id
WHERE 
  c1.deleted=0
  AND (@ContactID='All' OR (c2.first_name + ' ' + c2.last_name = @ContactID OR (c1.first_name + ' ' + c1.last_name = @ContactID)))
END

该存储过程运行良好，没有错误，但我的问题是如何使用我在此处列出的字段来更改它，以便按照我上面所描述的方式获取直接报告给每个人的下属。基本上，EmployeeName字段是每次的顶级（即报告参数），ReportsTo别名是您在图像中看到的报告字段。
我没有关于SSRS报告的问题，只是想知道如何修改查询，使得在这种情况下，如果我选择Kevin Bicking并将其传递给我的存储过程。它目前仅返回直接员工Kelvin Squires。但我想要的不仅仅是Kelvin，还有所有报告给Kelvin的人，以及可能是Kelvin下属的老板，但也有直接报告。
非常感谢任何帮助。谢谢你的时间！
编辑部分 我正在使用sql server 2005。有人要求表定义，请注意，我没有创建这个表，它是基于CRM的系统自动生成的：
USE [sugarcrm]
GO
/****** Object:  Table [dbo].[contacts]    Script Date: 07/22/2010 10:44:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[contacts](
    [id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [date_entered] [datetime] NULL,
    [date_modified] [datetime] NULL,
    [modified_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [created_by] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [deleted] [bit] NULL DEFAULT ('0'),
    [assigned_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [team_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [salutation] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [first_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [last_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [department] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [do_not_call] [bit] NULL DEFAULT ('0'),
    [phone_home] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_mobile] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_work] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_other] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [assistant] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [assistant_phone] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [lead_source] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [reports_to_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [birthdate] [datetime] NULL,
    [portal_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [portal_active] [bit] NOT NULL DEFAULT ('0'),
    [portal_password] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [portal_app] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [campaign_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [pk_contacts] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

解决方案

在这里得到大家的帮助后，我找到了解决方案。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
SET NOCOUNT ON;

--grab id of @contactid
DECLARE @Test varchar(36)
SELECT @Test = (SELECT id FROM contacts c1 WHERE c1.first_name + ' ' + c1.last_name = @ContactID)


;WITH StaffTree AS 
( 
    SELECT  
        c.id, 
        c.Title, 
        c.first_name, 
        c.last_name, 
        c.reports_to_id, 
        c.reports_to_id as Manager_id, 
        cc.first_name AS Manager_first_name, 
        cc.last_name as Manager_last_name, 
        cc.first_name + ' ' + cc.last_name AS [ReportsTo], 
        c.first_name + ' ' + c.last_name as EmployeeName,  
        1 AS LevelOf 
        FROM Contacts                  c 
            LEFT OUTER JOIN Contacts  cc ON c.reports_to_id=cc.id 
        WHERE c.id=@Test OR (@Test IS NULL AND c.reports_to_id IS NULL) 
    UNION ALL 
        SELECT  
        s.id, 
        s.Title, 
        s.first_name, 
        s.last_name, 
        s.reports_to_id, 
        t.id, 
        t.first_name, 
        t.last_name, 
        t.first_name + ' ' + t.last_name, 
        s.first_name + ' ' + s.last_name,
        t.LevelOf+1 
        FROM StaffTree            t 
            INNER JOIN Contacts  s ON t.id=s.reports_to_id 
    WHERE s.reports_to_id=@Test OR @Test IS NULL OR t.LevelOf>1 
)
SELECT * FROM StaffTree 

END
你正在使用哪个版本的SQL Server？ - Tom H
@Tom H. 我正在使用 SQL Server 2005。 - oJM86o
2个回答
6

根据原帖中的表格进行编辑：

这里是一个使用原帖中表格定义的列的示例，我的样本数据如下：

              1-Jerome
                |
              2-Joe
            /       \
     3-Paul          6-David
    /      \            /    \
 4-Jack  5-Daniel   7-Ian    8-Helen


--I only included the needed columns from the OP's table here
DECLARE @Contacts table (id varchar(36), first_name varchar(100), reports_to_id varchar(36))
INSERT @Contacts VALUES ('1','Jerome', NULL )
INSERT @Contacts VALUES ('2','Joe'   ,'1')
INSERT @Contacts VALUES ('3','Paul'  ,'2')
INSERT @Contacts VALUES ('4','Jack'  ,'3')
INSERT @Contacts VALUES ('5','Daniel','3')
INSERT @Contacts VALUES ('6','David' ,'2')
INSERT @Contacts VALUES ('7','Ian'   ,'6')
INSERT @Contacts VALUES ('8','Helen' ,'6')

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

输出：

@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
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

(8 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)
@KM - 我在这个问题的编辑中发布了我的表定义。我感谢您的帮助，但我尝试了您的示例，似乎无法应用到我的情况。您可以查看我的表定义（唯一相关的事情基本上是名称字段和ID，注意加入到表本身）。再次感谢KM！ - oJM86o
5
使用递归查询。MSDN关于此主题的文章使用了一个与您相似的示例。在您的情况下，您将选择Kevin的条目作为锚定定义。尝试以下内容（未经测试）：
CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
    WITH ManagerEmployee (ManagerID, EmployeeID, first_name, last_name, title)
    AS
    (
        -- Anchor
        SELECT ManagerID, EmployeeID, first_name, last_name, title
          FROM Contacts
         WHERE EmployeeID = @ContactID

        UNION ALL

        -- Recursion
        SELECT ManagerID, EmployeeID, first_name, last_name, title
          FROM Contacts c
          JOIN ManagerEmployee me ON (me.EmployeeID = c.ManagerID)
    )
    SELECT ManagerID,
           EmployeeID,
           first_name + ' ' + last_name AS EmployeeName,
           title as Title
    FROM ManagerEmployee
END
我已经读了几遍，你能否使用我的查询并向我展示如何做类似的事情。不幸的是，我不太懂T-SQL :( - oJM86o
我已经根据我的最佳猜测修改了答案，以满足你所需的SQL。 - Marcelo Cantos
递归CTE中肯定需要使用UNION ALL而不是UNION，在第二层中，您需要限定列名以避免连接时出现歧义的名称错误。 - Martin Smith
这绝对行不通，因为managerid和employeeid在select语句中不在同一个调用中。你必须内连接回同一张表才能真正获取ID字段。 - oJM86o
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接