Oracle SQL需求：基于可用余额筛选可开票合格交易行

问题分析 #

当前SQL的逻辑是判断到当前行的累计金额是否不超过可用余额，这与需求不符。需求允许中间累计暂时超过额度，只要最终选中的合格行（结合贷方行的抵消）总和不超过可用余额；同时允许跳过会导致超标的借方行，后续符合条件的行仍可被标记为合格。

核心需求逻辑：

• 所有贷方交易行（amount < 0）直接标记为Y，因为它们会抵消借方金额，扩大可用额度的覆盖范围。
• 借方交易行的最大可允许总和为 可用余额 + 所有贷方行的绝对值总和（贷方相当于给可用额度“扩容”）。
• 按Item升序处理借方行：若加入当前行后累计借方总和不超过最大允许值，则标记为Y并更新累计；否则标记为N，累计保持不变，继续处理后续行。

修正后的SQL #

WITH t1 AS (
    SELECT 1 item, 30 amount FROM dual 
    UNION SELECT 2 item, 40 amount FROM dual 
    UNION SELECT 3 item, 60 amount FROM dual 
    UNION SELECT 4 item, -60 amount FROM dual 
    UNION SELECT 5 item, 20 amount FROM dual
    UNION SELECT 6 item, 20 amount FROM dual 
    UNION SELECT 7 item, 5 amount FROM dual 
    UNION SELECT 8 item, 35 amount FROM dual 
),
total_credit AS (
    -- 计算所有贷方行的总绝对值（用于扩容可用额度）
    SELECT ABS(SUM(amount)) AS credit_sum FROM t1 WHERE amount < 0
),
sorted_data AS (
    -- 按Item排序并添加行号，用于递归处理
    SELECT t1.*, 
           ROW_NUMBER() OVER (ORDER BY item) AS rn
    FROM t1
),
recursive_cte AS (
    -- 递归初始行（第一行）
    SELECT 
        rn,
        item,
        amount,
        CASE 
            WHEN amount < 0 THEN 'Y'
            WHEN amount <= (100 + (SELECT credit_sum FROM total_credit)) THEN 'Y'
            ELSE 'N'
        END AS elig_for_inv,
        CASE 
            WHEN amount < 0 THEN 0
            WHEN amount <= (100 + (SELECT credit_sum FROM total_credit)) THEN amount
            ELSE 0
        END AS cum_debit
    FROM sorted_data
    WHERE rn = 1
    UNION ALL
    -- 递归处理后续行
    SELECT 
        s.rn,
        s.item,
        s.amount,
        CASE 
            WHEN s.amount < 0 THEN 'Y'
            WHEN (r.cum_debit + s.amount) <= (100 + (SELECT credit_sum FROM total_credit)) THEN 'Y'
            ELSE 'N'
        END AS elig_for_inv,
        CASE 
            WHEN s.amount < 0 THEN r.cum_debit
            WHEN (r.cum_debit + s.amount) <= (100 + (SELECT credit_sum FROM total_credit)) THEN r.cum_debit + s.amount
            ELSE r.cum_debit
        END AS cum_debit
    FROM recursive_cte r
    JOIN sorted_data s ON s.rn = r.rn + 1
)
-- 输出最终结果，按Item排序
SELECT item, amount, elig_for_inv
FROM recursive_cte
ORDER BY item;

逻辑说明 #

1. total_credit：计算所有贷方行的总绝对值，得到可用额度的扩容值。
2. sorted_data：将交易行按Item升序排列，添加行号用于递归逐行处理。
3. recursive_cte：通过递归模拟贪心选择过程：
   • 初始行单独处理，判断是否符合条件并初始化累计借方金额。
   • 后续行根据前一行的累计借方金额，判断当前行是否可加入（不超过最大允许总和），更新标记和累计值。

执行该SQL后，将得到与期望一致的输出结果。

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