如何创建一个SQL过程，将列中的条目逐个作为输入？

Question

这是我创建的表和一些初始值。

/*Make the table*/

CREATE TABLE PEOPLE(
ID int PRIMARY KEY,
NAME varchar(100) NOT NULL,
SUPERIOR_NAME varchar(100)
);

/*Give it some initial values*/

INSERT INTO PEOPLE VALUES(1, 'A',NULL), (2, 'B', 'E'), (3, 'C', 'A'), 
(4, 'D', 'A'), (5, 'E',NULL), (6, 'F', 'D');

我需要编写一个SQL过程来返回一个人的所有下属，包括所有下属等等。在这个例子中，如果我输入A，我应该得到C，D和F(D的下级，它是A的下级)作为输出。但我只能到达一个级别，即C和D。我如何使其适用于层次结构中的任意数量的级别？我看错了吗？

下面是我为某一级别编写的过程：

USE DB
GO

CREATE PROCEDURE SP_GETSUBS @NAME VARCHAR(100)

AS
BEGIN

IF @NAME IN (SELECT SUPERIOR_NAME FROM PEOPLE)
SELECT SUPERIOR_NAME AS "NAME", NAME AS "SUBORDINATE" FROM PEOPLE WHERE
SUPERIOR_NAME=@NAME;     
END

我正在考虑将第一级结果推入一个临时表并使用递归，但我不知道make a过程是如何逐个遍历列的条目的。有什么想法吗？我使用SQL Server Management Studio 2012。

Answer 1

使用自引用公用表表达式，并在您的选择中保留顶级经理(Boss)：

WITH OrganisationChart (Id, [Name], [Level], superior_name, [Boss]) 
AS
(
      SELECT 
            Id, [Name], 0 AS [Level], superior_name, name
      FROM
            dbo.people
      WHERE
            superior_name IS NULL
      UNION ALL
      SELECT
            emp.Id,
            emp.[Name],
            [Level] + 1,
            emp.superior_name,
            [Boss]
      FROM
            dbo.people emp
      INNER JOIN 
            OrganisationChart 
      ON
            emp.superior_name = OrganisationChart.name

)
SELECT 
    * 
FROM 
    OrganisationChart
WHERE 
    name != [Boss]

感谢Simon Ince为他撰写的文章Hierarchies WITH Common Table Expressions.

Answer 2

尝尝这个

CREATE PROCEDURE USP_GETSUBS
(
    @NAME VARCHAR(100)
) -- USP_GETSUBS 'A'
AS
BEGIN

    IF EXISTS (SELECT SUPERIOR_NAME FROM PEOPLE WHERE Name=@NAME)
    BEGIN
        WITH Subordinates AS
        (
           SELECT p.ID, p.Name, p.SUPERIOR_NAME
           FROM PEOPLE AS p 
           WHERE p.Name = @NAME
           UNION ALL
           SELECT p.ID, p.Name, p.SUPERIOR_NAME
           FROM PEOPLE AS p
           INNER JOIN Subordinates AS sub ON p.SUPERIOR_NAME = sub.Name
        )

        SELECT s.SUPERIOR_NAME AS "NAME",s.Name AS "SUBORDINATE"
        FROM Subordinates AS s
        WHERE s.SUPERIOR_NAME IS NOT NULL   
    END 

END

Answer 3

您应该在ID上链接上级名称，而不是在名称上。这是肯定的。

通过使用2个临时表和迭代，您可以在没有递归或CTE的情况下做到这一点。我将假设你有SuperiorID而不是Superior_Name

CREATE PROCEDURE SP_GETSUBS 
    @ID INT
AS
BEGIN

    DECLARE @allsubs AS TABLE(ID INT)
    DECLARE @subs AS TABLE(ID INT)
    DECLARE @temp AS TABLE(ID INT)

    --Get immediate subordinates of the ID passed in
    INSERT INTO @subs(ID)
    SELECT ID FROM PEOPLE WHERE SUPERIORID = @ID

    WHILE EXISTS(SELECT * FROM @subs)
    BEGIN
        DELETE @temp

        --copy the subordinate list
        INSERT INTO @temp
        SELECT (ID) FROM @subs

        DELETE @subs

        --get the subordinates' subordinates
        INSERT INTO @subs (ID)
        SELECT ID FROM PEOPLE WHERE SUPERIORID IN (
            SELECT ID FROM @temp
        )

        --add the subordinates to the full list
        INSERT INTO @allsubs (ID)
        SELECT ID FROM @temp

    END

    --select IDs from the full list
    SELECT * FROM PEOPLE WHERE ID IN (
        SELECT ID FROM @allsubs
    )

END

如果发生了太多的递归，这将不会引发问题-尽管这在SQLServer的后续版本中不会出现问题。

也是- You shouldn't name stored procedures beginning 'sp_'.