我需要将此查询从Oracle转换为SQL Server

Question

SELECT
    ffl2.fee_record_code,
    (SELECT max(fee_record_code)
     FROM fees_list ffl3 
     START WITH ffl3.fee_record_code = Nvl(ffl2.fes_associated_record, ffl2.fee_record_code)
     CONNECT BY PRIOR ffl3.fee_record_code = ffl3.fes_associated_record) final_record_code
FROM 
    fees_list ffl2

Answer 1

SQL Server没有通过连接。您需要使用递归CTE。

不幸的是，您不能将递归CTE放入子查询或派生表中。因此，解决您特定问题的最简单方法是使用内联表值函数

CREATE OR ALTER FUNCTION dbo.GetMaxRecords (@fee_record_code int)
RETURNS TABLE
AS RETURN

WITH cte AS (
    SELECT fee_record_code
    FROM fees_list ffl3 
    WHERE ffl3.fee_record_code = @fee_record_code

    UNION ALL

    SELECT fee_record_code
    FROM fees_list ffl3
    JOIN cte ON cte.fee_record_code = ffl3.fes_associated_record
) 
SELECT
  fee_record_code = MAX(cte.fee_record_code)
FROM cte;

go

SELECT
    ffl2.fee_record_code,
    final_record_code = (
      SELECT r.fee_record_code
      FROM dbo.GetMaxRecords( ISNULL(ffl2.fes_associated_record, ffl2.fee_record_code) ) r
    )
FROM 
    fees_list ffl2;