Oracle SQL中按订阅月生成付款日期及解决重复数据问题

你的问题出在没有为每个客户的递归层级做独立限制，默认的CONNECT BY会让所有行的层级递归相互交叉，从而产生大量重复记录。下面是两种可行的解决方法：

方法一：优化CONNECT BY语句 #

在原有SQL基础上，添加对当前客户ID的关联限制，同时避免递归循环：

SELECT ID, ADD_MONTHS(REGISTER_DATE, LEVEL) AS PAYMENT_DATE
FROM SUBLIST
CONNECT BY LEVEL <= SUBSCRIBED_MONTH
       -- 确保只对当前ID的行进行递归
       AND PRIOR ID = ID
       -- 防止Oracle检测到循环依赖（用SYS_GUID生成唯一值打破循环判断）
       AND PRIOR SYS_GUID() IS NOT NULL

方法二：使用递归CTE（更直观） #

如果是Oracle 11gR2及以上版本，可以用递归公共表表达式，逻辑更清晰，也不会产生重复：

WITH RECURSIVE PAYMENT_CTE AS (
    -- 初始化：每个客户的第1个付款月（注册日期+1个月）
    SELECT 
        ID, 
        ADD_MONTHS(REGISTER_DATE, 1) AS PAYMENT_DATE,
        SUBSCRIBED_MONTH,
        1 AS CURRENT_MONTH
    FROM SUBLIST
    WHERE SUBSCRIBED_MONTH >= 1 -- 过滤掉订阅月数为0的客户
    UNION ALL
    -- 递归生成后续月份
    SELECT 
        ID, 
        ADD_MONTHS(PAYMENT_DATE, 1),
        SUBSCRIBED_MONTH,
        CURRENT_MONTH + 1
    FROM PAYMENT_CTE
    WHERE CURRENT_MONTH < SUBSCRIBED_MONTH
)
SELECT ID, PAYMENT_DATE
FROM PAYMENT_CTE
ORDER BY ID, PAYMENT_DATE;

说明 #

• 方法一中的PRIOR ID = ID保证递归过程中只处理当前客户的行，不会和其他客户的行交叉；PRIOR SYS_GUID() IS NOT NULL是个小技巧，用来避免Oracle误判递归存在循环（因为每次生成的GUID都是唯一的，PRIOR后的GUID不会等于当前的）。
• 方法二的递归CTE从每个客户的第一个付款月开始，逐月累加直到订阅月数用完，逻辑更易懂，也更容易排查问题。

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