我有一張表，當我輸入父 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
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/shujuku/534689.html
標籤：数据库sql服务器sql-server-2008while循环
上一篇：將資料從一個表拆分到另一個表