SQL 2008 中的循环-解网

问：

我有一个表，当我输入父 ID 时，我需要提取所有子客户端 ID。代码将继续运行，直到 clientid = parentid。在下表中，如果我输入 parentid 01，结果将是 clientid 01、02 和 04。

如果循环不是最好的方法，那么最好的选择是什么？

客户端 ID	家长 ID	客户
01	01	客户01
02	01	客户02
03	100	客户03
04	02	客户04

DECLARE @Client varchar(20)
set @Client='1000'
WITH _RecursiveCTE AS
(
-- Base case
SELECT iClientID, iParentClientID, sClientCode
FROM tblClient
WHERE iClientID <> iParentClientID -- Prevents infinite recursion with 
your specific data model

UNION ALL

-- Recursive case
SELECT C.iClientID, C.iParentClientID, C.sClientCode
FROM _RecursiveCTE AS RC -- Notice the self-referenced CTE here creates 
the recursion
INNER JOIN tblClient AS C ON RC.iClientID = C.iParentClientID
)

SELECT iClientID, iParentClientID, sClientCode
FROM _RecursiveCTE
WHERE sClientCode = @Client

UNION ALL

-- Makes sure we don't miss out on the case we pre-filtered from the 
recursive CTE
SELECT iClientID, iParentClientID, sClientCode
FROM tblClient
WHERE iClientID = iParentClientID
AND sClientCode = @Client

SQL 服务器 sql-server-2008 while-loop

WITH _RecursiveCTE AS
(
    -- Base case
    SELECT ClientID, ParentID, ParentID AS OriginalParentID
    FROM dbo.YourTable
    
    UNION ALL

    -- Recursive case
    SELECT YC.ClientID, YC.ParentID, RC.OriginalParentID
    FROM _RecursiveCTE AS RC -- Notice the self-referenced CTE here creates the recursion
    INNER JOIN dbo.YourTable AS YC
        ON RC.ClientID = YC.ParentID
    WHERE RC.ClientID <> RC.ParentID -- Prevents infinite recursion with your data model
)

SELECT ClientID
FROM _RecursiveCTE
WHERE OriginalParentID = '01';

SQL 2008 中的循环

loop in SQL 2008

评论

评论