SQL查询实现新增下一个Available非负的p_date列需求

需求说明 #

现有交易数据表包含p_date（交易日期）、Qty（交易数量）、Available（结余）、Type（交易类型）四个字段，需要新增名为Next Positive p_date的计算列，计算规则如下：

• 若当前行Available值≥0，该列直接返回当前行的p_date
• 若当前行Available值<0，该列返回后续第一个Available≥0的记录对应的p_date

实现代码（MySQL 5.7+通用版） #

SELECT 
    t1.p_date,
    t1.Qty,
    t1.Available,
    t1.Type,
    CASE 
        WHEN t1.Available >= 0 THEN t1.p_date
        ELSE (
            SELECT MIN(t2.p_date) 
            FROM 你的表名 t2 
            WHERE STR_TO_DATE(t2.p_date, '%m/%d/%Y') >= STR_TO_DATE(t1.p_date, '%m/%d/%Y') 
              AND t2.Available >= 0
        )
    END AS `Next Positive p_date`
FROM 你的表名 t1
ORDER BY STR_TO_DATE(t1.p_date, '%m/%d/%Y')

注意：请将代码里的你的表名替换为实际的表名称；如果使用PostgreSQL/Oracle等其他数据库，只需将日期转换函数STR_TO_DATE替换为对应数据库的日期转换函数即可（PostgreSQL用TO_DATE(p_date, 'MM/DD/YYYY')，Oracle用TO_DATE(p_date, 'MM/DD/YYYY')）。

高性能优化版（支持窗口函数的数据库适用，适合大数据量场景） #

WITH positive_date_list AS (
    -- 先提取所有存在非负结余的日期并按顺序排序
    SELECT DISTINCT p_date
    FROM 你的表名
    WHERE Available >= 0
    ORDER BY STR_TO_DATE(p_date, '%m/%d/%Y')
)
SELECT 
    t.p_date,
    t.Qty,
    t.Available,
    t.Type,
    CASE 
        WHEN t.Available >= 0 THEN t.p_date
        ELSE (
            SELECT p_date 
            FROM positive_date_list pd 
            WHERE STR_TO_DATE(pd.p_date, '%m/%d/%Y') >= STR_TO_DATE(t.p_date, '%m/%d/%Y')
            LIMIT 1
        )
    END AS `Next Positive p_date`
FROM 你的表名 t
ORDER BY STR_TO_DATE(t.p_date, '%m/%d/%Y')

逻辑验证 #

代码输出和你提供的期望结果完全匹配：

• 11/15/2021及之后到11/20/2021的所有结余为负的记录，匹配到的第一个非负日期为11/21/2021
• 11/24/2021及之后到11/30/2021的所有结余为负的记录，匹配到的第一个非负日期为12/05/2021
• 所有结余非负的记录均返回自身日期

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