主页 / dba / 问题 / 279912
Accepted
SQLserving
Asked: 2020-11-19 10:02:55 +0800 CST

带分区的递归 CTE

  • 772

我在 MS SQL SERVER 2014 中有一个这样的表：

ID|Race|Lap  
1 |21  |11  
2 |21  |NULL
3 |21  |NULL  
4 |21  |NULL  
5 |29  |65  
6 |29  |NULL  
7 |29  |NULL  
8 |29  |NULL

我正在尝试通过根据第一个值向其添加 1 来填充 Lap 列。分区基于 Race 列。这样的结果将是最终结果：

ID|Race|Lap  
1 |21  |11  
2 |21  |12
3 |21  |13  
4 |21  |14  
5 |29  |65  
6 |29  |66  
7 |29  |67  
8 |29  |68

可能还有其他方法可以做到这一点，但我宁愿坚持使用递归 CTE。有没有办法做到这一点？

sql-server recursive

3 个回答

  1. Best Answer

    这将产生预期的结果：

    create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);


with CTE as
(select race, ROW_NUMBER() over (partition by race  order by race) "extra_lap" from #demo where lap is null),
CTE2 as 
(select race, lap "lap" from #demo where lap is not null)
select race, lap from CTE2
union 
select CTE.race, CTE2.lap + CTE.extra_lap "lap" from CTE join CTE2 on CTE.race=CTE2.race

drop table #demo;
    • 2

  2. 还有另一种使用窗口函数的方法：

    create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);

SELECT * , IIF(lap IS NULL , FIRST_VALUE(lap) OVER (PARTITION BY s.race ORDER BY id ) + RANK() OVER ( PARTITION BY s.race ORDER BY id ) -1 , lap)
 FROM #demo AS s
    • 2

  3. 对于这种情况，递归 CTE 可以如下使用，无需分区。

    --This temp table created to store sample values
SELECT Id,Race,Lap 
INTO #LapRecords 
FROM (VALUES (1,21,11),(2,21,NULL),(3,21,NULL),(4,21,NULL),(5,29,65),(6,29,NULL),(7,29,NULL),(8,29,NULL)) LapRecords (Id,Race,Lap);

--Recursive CTE
WITH LapRecordsCTE (Id,Race,Lap) AS
    (
        SELECT Id,Race,Lap FROM #LapRecords WHERE Lap IS NOT NULL
        UNION ALL 
        SELECT LapRecords.Id,LapRecords.Race,LapRecordsCTE.Lap+1
        FROM #LapRecords AS LapRecords JOIN LapRecordsCTE 
        ON LapRecords.Id=LapRecordsCTE.Id+1 AND LapRecords.Race=LapRecordsCTE.Race
    )

SELECT Id,Race,Lap FROM LapRecordsCTE ORDER BY Race,Lap;

--Temp table droped to clean up workspace
Drop Table #LapRecords
    • 0

相关问题