如何在SQL Server中使用迭代？

问题描述：

我需要找到每个信用顾问他/她的区域经理。如何在SQL Server中使用迭代？

我们的网络是这样的：

CareerSystemPositionId CareerSystemPositionCode_Primary PositionShortcut OriginalPositionShortcut PositionShortcutTranslationId 
2 -1 PM PM 30 Profi Manager 
3 0 RM RM 31 Region Manager 
4 1 AM AM 32 Area Manager 
5 2 TM TM 33 Team Leader 
6 3 CAS ÚPS 34 Senior Credit Advisor 
7 4 CA ÚP 35 Senior Credit

我已经试过：

-- Iterate until encountering AM position ID 
:WAY 

;WITH CA AS 
(
    SELECT 
     ca.CreditAdvisorId AS CA_ID, 
     ca.CreditAdvisorCode_Primary AS CA_Code, 
     ca.CreditAdvisorParentID AS ParentID, 
     ca.CareerSystemPositionId AS PositionID, 
     ca.ClosestManagerId AS CloseManID 
    FROM 
     dim.CreditAdvisor ca 
    WHERE 
     ca.CreditAdvisorId = @CA_id --nenacitame vsechny zaznamy 
) 
SET @CA_id = (SELECT ParentID FROM CA) 

IF NOT (CA.PositionID <= 4) GOTO WAY; 

-- Final function result 
SELECT 
    IIF(ca.PositionID = 4, ca.CA_ID, 0) AS AM_Id, -- when no AM in a network, return 0 
    IIF(ca.PositionID = 4, ca.CA_Cod, 0) AS AM_code 
FROM 
    CA ca

任何帮助，将不胜感激。

在此先感谢

你能得到所需要的输出的例子吗？ – cdsln

SQL不是一种命令式语言。它处理表格值。你需要使用[递归]（https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example） – Caleth

答

你需要指定源递归表表达式，然后选择你想要的数据从它

WITH CA AS 
(
    SELECT 
     ca.CreditAdvisorId AS CA_ID, 
     ca.CreditAdvisorCode_Primary AS CA_Code, 
     ca.CreditAdvisorParentID AS ParentID, 
     ca.CareerSystemPositionId AS PositionID, 
     ca.ClosestManagerId AS CloseManID 
    FROM 
     dim.CreditAdvisor ca 
    WHERE 
     ca.CreditAdvisorId = @CA_id --nenacitame vsechny zaznamy 

    UNION ALL 

    SELECT 
     dca.CreditAdvisorId AS CA_ID, 
     dca.CreditAdvisorCode_Primary AS CA_Code, 
     dca.CreditAdvisorParentID AS ParentID, 
     dca.CareerSystemPositionId AS PositionID, 
     dca.ClosestManagerId AS CloseManID 
    FROM 
     dim.CreditAdvisor dca 
    JOIN CA ON dca.CreditAdvisorId = CA.ParentID 
    WHERE 
     CA.PositionID <= 4 
) 
SELECT 
    ca.CA_ID AS AM_Id 
    ca.CA_Code AS AM_code 
FROM 
    CA ca 
WHERE 
    ca.PositionId = 4;

如何在SQL Server中使用迭代？

相关推荐