基于最大日期及多条件筛选表数据的SQL实现方案咨询

数据老陈

2026-8-10

问题描述

需要从表中按Inv分组筛选数据，满足以下要求：

每组选取activity_date最大且Open_amt <> 0的行；
若某组最大activity_date对应的行是Open_amt = 0且State = 'closed'，则该组整体排除，不返回任何数据。

原SQL语句（存在问题，无返回结果）：

SELECT inv,account,activity,seq,st_date,open_amt,State,activity_date 
FROM table
WHERE inv = 'test_inv'
AND State = 'issued'
AND activity_date = (select MAX (activity_date) FROM table)

表数据：

Inv #	Account #	Activity	State	Open_Amt	Last Activity Dt	Seq	St_date
123	Customer1	Act-1	Issued	12.50	2022-02-18	455	2022-01-04
123	Customer1	Act-2	Closed	0.00	2022-03-05	567	2022-01-04
345	Customer2	Act-1	Issued	15.00	2022-02-18	467	2022-01-12
345	Customer2	Act-2	Issued	09.35	2022-02-25	488	2022-01-12
678	Customer3	Act-1	Issued	30.50	2022-03-20	589	2022-01-23
678	Customer3	Act-2	Closed	00.00	2022-03-30	623	2022-01-23
678	Customer3	Act-3	Issued	30.50	2022-04-02	788	2022-01-23
678	Customer3	Act-4	Issued	05.50	2022-04-10	988	2022-01-23

期望输出：

Inv #	Account #	Activity	State	Open_Amt	Last Activity Dt	Seq	St_date
345	Customer2	Act-2	Issued	09.35	2022-02-25	488	2022-01-12
678	Customer3	Act-4	Issued	05.50	2022-04-10	988	2022-01-23

原SQL问题分析

硬编码inv = 'test_inv'，但表中无对应数据，直接过滤全部行；
子查询MAX(activity_date)取全表最大日期，而非按Inv分组的最大日期，逻辑错误；
提前过滤State = 'issued'，会漏掉需要判断的Closed状态行（比如Inv=123的最大日期行是Closed，需排除整个组）。

解决方案：使用窗口函数分组排序

用ROW_NUMBER()窗口函数按Inv分组、按activity_date降序排序，标记每组最新行后筛选符合条件的记录：

WITH ranked_data AS (
    SELECT 
        inv, account, activity, seq, st_date, open_amt, State, 
        `Last Activity Dt` AS activity_date,
        -- 按Inv分组，活动日期降序排名，每组第一为最新行
        ROW_NUMBER() OVER (PARTITION BY inv ORDER BY `Last Activity Dt` DESC) AS rn
    FROM your_table  -- 替换为实际表名
)
SELECT 
    inv AS `Inv #`, 
    account AS `Account #`, 
    activity AS Activity, 
    State, 
    open_amt AS Open_Amt, 
    activity_date AS `Last Activity Dt`, 
    seq AS Seq, 
    st_date AS St_date
FROM ranked_data
WHERE rn = 1  -- 取每组最新行
-- 排除最新行是Open_amt=0且State='Closed'的组，同时保证返回行Open_amt≠0
AND NOT (open_amt = 0 AND State = 'Closed')
AND open_amt <> 0;

逻辑说明

CTE部分：给每个Inv分组内的行按活动日期降序排名，rn=1即为该组最新行；
筛选逻辑：
- 先取每组最新行；
- 排除最新行是Open_amt=0且State='Closed'的组；
- 确保最终返回的行Open_amt <> 0；
针对Inv=123：最新行符合Open_amt=0且State='Closed'，会被排除；
针对Inv=345和678：最新行均满足Open_amt≠0且State='Issued'，会被保留，与期望输出一致。

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