我如何找到不是最新的记录，没有特定值的字段?

Question

我有一个合同付款记录表。每份合同按月付款。该表包含以下字段:

• ID(创建付款记录时自动分配)
• payment_number(数字字符数组，按月递增，例如"1"， "2"， "3")
• 付款状态("已接收"、"已拒绝"、"已批准"、"已处理")
• contract_ID(由字母和数字组成的字符数组)

我有一个理论,当一个新的付款收到,之前的付款必须被处理。一个月的时间足够处理前一笔付款了。

为了验证这一点，我想知道如何找到反例。更具体地说，在每个合同的付款记录中，我如何找到所有不是该合同的最新付款，但状态不是"已处理"的付款记录?

ID   contract_ID  payment_number payment_status
1    contract1      1           Processed
2    contract1      2           Processed
3    contract2      1          Processed
4    contract2      2           Approved
5    contract1      3           Received
6    contract2      3           Processed

谢谢。

Answer 1

这是我在各种形式中经常使用的技巧:

with cte as (
select *,
row_number() over (partition by contract_ID order by id desc) as rn
from (values 
(1, 'contract1', 1, 'Processed'),
(2, 'contract1', 2, 'Processed'),
(3, 'contract2', 1, 'Processed'),
(4, 'contract2', 2, 'Approved'),
(5, 'contract1', 3, 'Received'),
(6, 'contract2', 3, 'Processed')
) as d(ID, contract_ID, payment_number, payment_status)
)
select *
from cte
where rn <> 1
and payment_status <> 'Processed';

大部分的解决方案是增加一个row_number()列。它所做的是枚举每个合约(partition by contract_id)中的行，按从最新到最旧(order by id desc)的顺序。一旦你有了这个，表达你的逻辑就不是最新的了。并且"具有除'已处理'"之外的状态"很容易用SQL谓词表示。