在 SQL Server 中进行递归自联接的最简单方法是什么?我有一张这样的桌子:
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
除了基于一堆连接的固定深度响应之外,我对此有点坚持。这会发生,因为我们不会有很多级别,但我想正确地做到这一点。
谢谢!克里斯。
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 岁的父母:将 row_number() 格式化为 str(5)
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 中执行此操作,您可以在 sproc 中大致执行上述操作,但无法从中选择 - 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 ;
PersonID = theIdYouAreLookingFor
而不是ParentID IS NULL
。WITH
子句中的最顶层(锚)查询定义。如果您需要详细信息,请在 sqlfiddle.com 上创建一个小提琴并在此处发布链接。