根据排除逻辑获取数据

Question

添加了更多上下文的表，我需要基于以下两个条件的数据

1. Max (activey_date) with Open_amt <>0
2. 排除Open_amt = '0'和State = 'closed'的Max (activity_date)行

<表类> 发票# 账户# 活动 国家 Open_Amt 最后活动Dt > St_date tbody><<tr>123Customer1第1幕发表12.502022-02-184552022-01-04123Customer1第2幕关闭0.002022-03-055672022-01-04345Customer2第1幕发表15.002022-02-184672022-01-12345Customer2第2幕发表09.352022-02-254882022-01-12678Customer3第1幕发表30.502022-03-205892022-01-23678Customer3第2幕关闭00.002022-03-306232022-01-23678Customer3法3发表30.502022-04-027882022-01-23678Customer3四绝发表05.502022-04-109882022-01-23

我不确定您是否真的运行mysql给定的列名。这里是mysql中的查询:

with
latest as (select Inv, Max(Last_Activity_Dt) as date
from tbl group by (Inv))
select t.*
from latest as lt
left join tbl as t
on lt.Inv = t.Inv
and lt.date = t.Last_Activity_Dt
where t.state <> 'Closed' and Open_Amt <> 0;

它使用mysql 8上可用的CTE。CTE用于根据给定客户的Inv获取所有latest记录。

小提琴:https://www.db-fiddle.com/f/5fAJen86r2qReZYHdhxtcj/0