我有一個表，當我輸入父 ID 時，我需要提取所有子客戶端 ID。代碼將繼續運行，直到 clientid = parentid。在下表中，如果我輸入 parentid 01，結果將是 clientid 01、02 和 04。
如果回圈不是最好的方法，那么最好的選擇是什么？
| 客戶編號 | 父母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
uj5u.com熱心網友回復：
正如評論中提到的，遞回 CTE通常是解決樹型/層次結構問題的方法。在關系資料庫中，回圈通常不是一個很好的工具，它旨在通過基于集合的解決方案來解決關系問題。
遞回 CTE 示例：
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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/518435.html
標籤：sqlsql服务器sql-server-2008while循环
上一篇：301重定向不適用于索引檔案