재귀적인 자기 결합을 하는 가장 간단한 방법은?

재귀적인 자기 결합을 하는 가장 간단한 방법은?

SQL Server에서 재귀적 자가 조인을 수행하는 가장 간단한 방법은 무엇입니까?다음과 같은 테이블이 있습니다.

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

그리고 저는 특정 인물부터 시작하여 계층과 관련된 기록만을 얻을 수 있기를 원합니다.그래서 개인별로 CJ의 위계질서를 요청하면,ID =1 다음과 같은 정보를 얻을 수 있습니다.

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

EB의 경우 다음과 같은 이점이 있습니다.

PersonID | Initials | ParentID
2          EB         1
4          SW         2

저는 이것에 약간 집착하고 있습니다. 여러 조인을 기반으로 한 고정된 깊이의 응답을 제외하고는 어떻게 해야 할지 생각할 수 없습니다.우리는 레벨이 많지 않을 것이기 때문에 이렇게 하면 되지만, 나는 그것을 제대로 하고 싶습니다.

고마워요! 크리스.

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

순서 조건을 추가하여 트리 순서를 보존할 수 있습니다.

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

변경을 통해ORDER BY형제자매의 순서를 변경할 수 있는 조건입니다.

CTE를 사용하면 다음과 같은 방법으로 수행할 수 있습니다.

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

큰 테이블에 대한 변경 사항이 있는 Quassnoi 쿼리입니다.10명 이상의 자녀를 둔 부모: str(5) the row_number()로 형성

WITH    q AS 
        (
        SELECT  m.*, CAST(str(ROW_NUMBER() OVER (ORDER BY m.ordernum),5) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    #t m
        WHERE   ParentID =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

SQL 2005 이상에서는 CTE가 표시된 예와 같은 표준 방법입니다.

SQL 2000, UDF를 사용하면 할 수 있습니다.

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 personID=@PersonID

    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

(2005년에 작동할 예정이지만 표준 방식은 아닙니다.그렇긴 하지만, 만약 당신이 일하는 것이 더 쉽다는 것을 알게 된다면, 그것을 가지고 달려라)

SQL7에서 이 작업을 수행해야 하는 경우 대략 위의 작업을 저장 프로시저에서 수행할 수 있지만 선택할 수 없습니다. SQL7은 UDF를 지원하지 않습니다.

가 CTE 재귀 개념을 이해하는 데 도움이 되도록 다음을 확인합니다.

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)

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `myprocDURENAME`$$

CREATE DEFINER=`root`@`%` PROCEDURE `myprocDURENAME`( IN grp_id VARCHAR(300))
BEGIN
   SELECT h.ID AS state_id,UPPER(CONCAT( `ACCNAME`,' [',b.`GRPNAME`,']')) AS state_name,h.ISACTIVE  FROM accgroup b JOIN (SELECT get_group_chield (grp_id) a) s ON FIND_IN_SET(b.ID,s.a) LEFT OUTER JOIN acc_head h ON b.ID=h.GRPID WHERE h.ID IS NOT NULL AND H.ISACTIVE=1;
    END$$

DELIMITER ;

////////////////////////

DELIMITER $$

 

DROP FUNCTION IF EXISTS `get_group_chield`$$

CREATE DEFINER=`root`@`%` FUNCTION `get_group_chield`(get_id VARCHAR(999)) RETURNS VARCHAR(9999) CHARSET utf8
BEGIN
     DECLARE idd VARCHAR(300);
   DECLARE get_val VARCHAR(300);
    DECLARE get_count INT;
SET idd=get_id;
   
SELECT  GROUP_CONCAT(id)AS t,COUNT(*) t1 INTO get_val,get_count FROM accgroup ag JOIN (SELECT idd AS n1) d ON FIND_IN_SET(ag.PRNTID,d.n1);
SELECT   COUNT(*) INTO get_count FROM accgroup WHERE PRNTID IN (idd);
 WHILE get_count >0 DO
 SET idd=CONCAT(idd,',', get_val); 
SELECT  GROUP_CONCAT(CONCAT('', id ,'' ))AS t,COUNT(*) t1 INTO get_val,get_count FROM accgroup ag JOIN (SELECT get_val AS n1) d ON FIND_IN_SET(ag.PRNTID,d.n1);
   END WHILE;
   RETURN idd;
-- SELECT id FROM acc_head WHERE GRPID  IN (idd);
    END$$

DELIMITER ;

언급URL : https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join