如何按条件获取同一昵称同日期下负total值之前的有效行？

需求规则总结 #

• 排除所有total为负数的行
• 对于同一nickname且同一date的订单组：
  • 如果组内存在total为负的订单，仅保留该负订单之前的所有total非负订单
• 不同date的订单组互不影响，即使其他日期存在负total，当前日期的订单仍按规则处理

SQL实现代码 #

WITH order_groups AS (
    SELECT 
        *,
        -- 给每个nickname+date的组按orderid排序生成序号
        ROW_NUMBER() OVER (PARTITION BY nickname, date ORDER BY orderid) AS row_num,
        -- 定位当前组中第一个total为负的行的序号
        MIN(CASE WHEN total < 0 THEN row_number() OVER (PARTITION BY nickname, date ORDER BY orderid) END) OVER (PARTITION BY nickname, date) AS first_neg_row
    FROM orders
)
SELECT 
    orderid, nickname, date, total
FROM order_groups
WHERE 
    total >= 0 -- 排除本身total为负的行
    -- 组内无负订单则全部保留；有负订单则只保留其之前的行
    AND (first_neg_row IS NULL OR row_num < first_neg_row)
ORDER BY orderid;

逻辑说明 #

1. CTE分组处理：
   • 用ROW_NUMBER()给每个nickname+date分组内的订单按orderid生成递增序号，明确订单的先后顺序
   • 通过MIN(CASE...) OVER()捕获当前组中第一个total为负的订单序号，无负订单时该值为NULL
2. 主查询筛选：
   • 先过滤掉total为负的行
   • 针对有负订单的组，仅保留序号小于第一个负订单序号的行；无负订单的组则保留所有total非负的行

该方案完全匹配预期结果，同时满足不同日期组独立判断的规则。

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