我真的很困扰，要创建'level1 question'和'level2 question'列的一些代码。
填充这些列的逻辑是：
- 当级别=1且类型=组时，'L1'列为question
- 当级别=2且类型=组时，'L2'列为question
但我找不到创建'level1 question'和'level2 question'的有效方法，就像示例中那样。
我尝试使用以下代码，但速度非常慢。在500万行上花了大约2个小时。
'level1 question' = 
                (select top 1 L1 
                from table2 t2
                where L1 is not null
                and t1.Sort >= t2.Sort and t1.ID = t2.ID
                order by id , sort DESC)
, 'level2 question' = 
            (select top 1 L2 
            from table2 t2
            where L2 is not null AND (Level >2 OR Type = 'Group')
            and t1.Sort >= t2.Sort and t1.ID = t2.ID
            order by id , sort DESC)

select t.*,
    last_value(case when level = 1 and type = 'Group' then question end) 
        ignore nulls
        over(partition by id order by sort) as level1_question,
    last_value(case when level = 2 and type = 'Group' then question end) 
        ignore nulls
        over(partition by id order by sort) as level2_question
from mytable t

select t.*,
    max(question1) over(partition by id, grp1 order by sort) as level1_question,
    max(question2) over(partition by id, grp2 order by sort) as level2_question
from (
    select t.*,
        count(question1) over(partition by id order by sort) grp1,
        count(question2) over(partition by id order by sort) grp2
    from mytable t
    cross apply (
        values (
            case when level = 1 and type = 'Group' then question end,
            case when level = 2 and type = 'Group' then question end
        ) 
    ) as v(question1, question2)
) t