实现递归自联接的最简单方法是什么？

在 SQLServer 中实现递归自连接的最简单方法是什么:

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

我希望能够得到的记录只有相关的层次结构开始与特定的人。因此，如果我通过 PersonID = 1请求 CJ 的层次结构，我会得到:

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

我对此有点困惑，除了基于一堆连接的固定深度响应之外，我无法思考如何做到这一点。这将做因为它发生，因为我们不会有很多水平，但我想做的正确。

谢谢，克里斯。

124622 次浏览
小开
最佳答案 
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
小开

SQL 2005或更高版本，CTE 是按照所示示例进行操作的标准方法。

SQL2000，您可以使用 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年起作用，只是不是标准的方式做到这一点。也就是说，如果你发现这是一种更简单的工作方式，那就去做吧)

If you really need to do this in SQL7, you can do roughly the above in a sproc but couldn't select from it - SQL7 doesn't support UDFs.

小开

对大型表进行更改的 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
小开

Check following to help the understand the concept of CTE recursion

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 ;

提交答案