wake-up-neo.com

Einfachste Möglichkeit, einen rekursiven Self-Join durchzuführen?

Was ist der einfachste Weg, eine rekursive Selbstverknüpfung in SQL Server durchzuführen? Ich habe einen Tisch wie diesen:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2
5          YT         NULL
6          IS         5

Und ich möchte in der Lage sein, die Datensätze nur mit Bezug auf eine Hierarchie zu erhalten, die mit einer bestimmten Person beginnt. Wenn ich also CJs Hierarchie mit PersonID = 1 anforderte, würde ich Folgendes erhalten:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2

Und für EBs würde ich bekommen:

PersonID | Initials | ParentID
2          EB         1
4          SW         2

Ich bin ein bisschen hängen geblieben. Ich kann mir nicht vorstellen, wie es geht, abgesehen von einer Antwort mit fester Tiefe, die auf einer Reihe von Verknüpfungen basiert. Das würde so sein, weil es nicht viele Stufen gibt, aber ich würde es gerne richtig machen.

Vielen Dank! Chris.

87
Chris
WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

Durch das Hinzufügen der Bestellbedingung können Sie die Baumreihenfolge beibehalten:

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

Durch Ändern der ORDER BY-Bedingung können Sie die Reihenfolge der Geschwister ändern.

91
Quassnoi

Mit CTEs können Sie dies auf diese Weise tun

DECLARE @Table TABLE(
        PersonID INT,
        Initials VARCHAR(20),
        ParentID INT
)

INSERT INTO @Table SELECT     1,'CJ',NULL
INSERT INTO @Table SELECT     2,'EB',1
INSERT INTO @Table SELECT     3,'MB',1
INSERT INTO @Table SELECT     4,'SW',2
INSERT INTO @Table SELECT     5,'YT',NULL
INSERT INTO @Table SELECT     6,'IS',5

DECLARE @PersonID INT

SELECT @PersonID = 1

;WITH Selects AS (
        SELECT *
        FROM    @Table
        WHERE   PersonID = @PersonID
        UNION ALL
        SELECT  t.*
        FROM    @Table t INNER JOIN
                Selects s ON t.ParentID = s.PersonID
)
SELECT  *
FROm    Selects
20
Adriaan Stander

Die Quassnoi-Abfrage mit einer Änderung für große Tabelle. Eltern mit mehr Kindern als 10: Formieren der Zeilenanzahl () als str (5)

 WITH q AS 
 (
 SELECT m. *, CAST (str (ROW_NUMBER () ÜBER (BESTELLEN VON m.ordernum), 5) ALS VARCHAR (MAX)) COLLATE Latin1_General_BIN AS bc 
 FROM #t m .__ 0 
 UNION ALL 
 SELECT m. *, Q.bc + '.' + Str (ROW_NUMBER () OVER (PARTITION BY m.ParentID ORDER BY m.ordernum), 5) COLLATE Latin1_General_BIN 
 FROM t m 
 JOIN q 
 ON m.parentID = q.DBID 
) 
 SELECT * 
 FROM q 
 ORDER BY 
 bc 

4
guille

CTEs sind in SQL 2005 oder höher die Standardmethode gemäß den gezeigten Beispielen.

SQL 2000 können Sie mit UDFs machen - 

CREATE FUNCTION udfPersonAndChildren
(
    @PersonID int
)
RETURNS @t TABLE (personid int, initials nchar(10), parentid int null)
AS
begin
    insert into @t 
    select * from people p      
    where [email protected]

    while @@rowcount > 0
    begin
      insert into @t 
      select p.*
      from people p
        inner join @t o on p.parentid=o.personid
        left join @t o2 on p.personid=o2.personid
      where o2.personid is null
    end

    return
end

(was 2005 funktionieren wird, es ist einfach nicht die Standardmethode.)

Wenn Sie dies wirklich in SQL7 tun müssen, können Sie dies ungefähr in einem Sproc tun, aber Sie konnten keine Auswahl treffen.

2
eftpotrm

Überprüfen Sie Folgendes, um das Konzept der CTE-Rekursion zu verstehen

DECLARE
@startDate DATETIME,
@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
    SELECT
        YEAR(@startDate) AS 'yr',
        MONTH(@startDate) AS 'mm',
        DATENAME(mm, @startDate) AS 'mon',
        DATEPART(d,@startDate) AS 'dd',
        @startDate 'new_date'
    UNION ALL
    SELECT
        YEAR(new_date) AS 'yr',
        MONTH(new_date) AS 'mm',
        DATENAME(mm, new_date) AS 'mon',
        DATEPART(d,@startDate) AS 'dd',
        DATEADD(d,1,new_date) 'new_date'
    FROM CTE
    WHERE new_date < @endDate
    )
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
0