如何修正Connect By查询以生成两日期间的月度拆分结果？

你的查询核心问题是connect by level <= accrual_length未指定分组边界，导致Oracle跨所有行递归生成冗余数据。需要让每个机构-方案组合独立生成对应行数的月份序列，以下两种方法可解决：

方法一：修改Connect By子句 #

通过限定递归仅在同一机构-方案组内进行，避免跨组关联：

select distinct
    institution_name as 机构,
    protocol_no as 方案,
    to_char(add_months(activation_date, level-1), 'yyyy-MM') as 月份
from (
    -- 你的内查询逻辑保持不变
    select
        sv_pcl_institution.protocol_no,
        institution_name,
        case
            when institution_name = 'A' then cc.activation_date
            else inst.activation_date
        end as activation_date,
        case
            when (status != 'OPEN TO ACCRUAL' and institution_name = 'A') then cc.accrual_end_date
            when (status != 'OPEN TO ACCRUAL' and institution_name != 'A') then inst.accrual_end_date
            else null
        end as accrual_end_date,
        case
            when institution_name = 'A' then trunc(months_between(nvl(cc.accrual_end_date, SYSDATE), cc.activation_date))+1
            else trunc(months_between(nvl(inst.accrual_end_date, SYSDATE), inst.activation_date))+1
        end as accrual_length
    from sv_pcl_institution

    left join (
        select
            protocol_no,
            min(open_from_date) as activation_date,
            max(open_thru_date) as accrual_end_date
        from sv_pcl_open_status
        group by
            protocol_no
    ) cc
    on cc.protocol_no = sv_pcl_institution.protocol_no
    and sv_pcl_institution.institution_name = 'A'

    left join (
        select
            protocol_no,
            institution,
            min(inst_open_from_date) as activation_date,
            max(inst_open_thru_date) as accrual_end_date
        from sv_pcl_inst_open_status
        group by
            protocol_no,
            institution
    ) inst
    on inst.protocol_no = sv_pcl_institution.protocol_no
    and inst.institution = sv_pcl_institution.institution_name
    
    where sv_pcl_institution.institution_name is not null
    and (cc.activation_date is not null or inst.activation_date is not null)
)
connect by level <= accrual_length
and prior protocol_no = protocol_no
and prior institution_name = institution_name
and prior sys_guid() is not null;

关键修改说明：

• prior protocol_no = protocol_no 和 prior institution_name = institution_name：限定递归仅在同一个机构-方案组合内执行，确保每个分组独立生成月份序列。
• prior sys_guid() is not null：避免因重复分组键产生递归循环，sys_guid()每次生成唯一值，保证递归的唯一性。
• distinct：消除可能出现的重复行（若内查询中机构-方案已唯一，可尝试去掉测试）。

方法二：使用递归CTE（Oracle 11gR2及以上支持） #

递归CTE逻辑更直观，可读性更强，不易出错：

with recursive_monthly as (
    -- 基础查询：获取每个机构-方案的初始月份
    select
        institution_name as 机构,
        protocol_no as 方案,
        activation_date,
        accrual_length,
        1 as current_level,
        to_char(activation_date, 'yyyy-MM') as 月份
    from (
        -- 你的内查询逻辑保持不变
        select
            sv_pcl_institution.protocol_no,
            institution_name,
            case
                when institution_name = 'A' then cc.activation_date
                else inst.activation_date
            end as activation_date,
            case
                when (status != 'OPEN TO ACCRUAL' and institution_name = 'A') then cc.accrual_end_date
                when (status != 'OPEN TO ACCRUAL' and institution_name != 'A') then inst.accrual_end_date
                else null
            end as accrual_end_date,
            case
                when institution_name = 'A' then trunc(months_between(nvl(cc.accrual_end_date, SYSDATE), cc.activation_date))+1
                else trunc(months_between(nvl(inst.accrual_end_date, SYSDATE), inst.activation_date))+1
            end as accrual_length
        from sv_pcl_institution

        left join (
            select
                protocol_no,
                min(open_from_date) as activation_date,
                max(open_thru_date) as accrual_end_date
            from sv_pcl_open_status
            group by
                protocol_no
        ) cc
        on cc.protocol_no = sv_pcl_institution.protocol_no
        and sv_pcl_institution.institution_name = 'A'

        left join (
            select
                protocol_no,
                institution,
                min(inst_open_from_date) as activation_date,
                max(inst_open_thru_date) as accrual_end_date
            from sv_pcl_inst_open_status
            group by
                protocol_no,
                institution
        ) inst
        on inst.protocol_no = sv_pcl_institution.protocol_no
        and inst.institution = sv_pcl_institution.institution_name
        
        where sv_pcl_institution.institution_name is not null
        and (cc.activation_date is not null or inst.activation_date is not null)
    )
    union all
    -- 递归查询：生成后续月份，直到达到应计时长
    select
        机构,
        方案,
        add_months(activation_date, 1),
        accrual_length,
        current_level + 1,
        to_char(add_months(activation_date, current_level), 'yyyy-MM') as 月份
    from recursive_monthly
    where current_level < accrual_length
)
select 机构, 方案, 月份
from recursive_monthly
order by 机构, 方案, 月份;

逻辑说明：

• 基础部分获取每个机构-方案的激活日期所在月，作为初始行。
• 递归部分每次在上一个月份基础上加1个月，直到当前层级（current_level）等于应计时长（accrual_length）。
• 最后排序结果，确保输出顺序符合预期。

内容的提问来源于stack exchange，提问作者jshaub