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
uj5u.com熱心網友回復：
SQL Server 沒有CONNECT BY. 您需要使用遞回 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/467496.html
上一篇：根據多列獲取重復行的索引列