如何在SQL中基于日期查询记录并获取其交易内全局序号？

API 探路者

2026-8-6

解决方案

你需要先给每个transaction_id分组内的所有记录计算全局序号，再应用日期过滤条件，这样得到的序号才是分组内的全局序号，而非当前查询结果的局部序号。

核心逻辑

原来的SQL是先过滤日期再计算序号，ROW_NUMBER()仅针对过滤后的结果集排序，得到的是局部序号。正确流程是：先对全表按transaction_id分组、按业务顺序（比如id或date）排序，生成每个记录在分组内的全局序号，再筛选符合日期条件的记录。

实现代码

用CTE（公共表达式）可以清晰实现这个逻辑：

WITH numbered_transactions AS (
    SELECT 
        *,
        -- 按transaction_id分组，按id排序生成全局序号
        ROW_NUMBER() OVER(PARTITION BY transaction_id ORDER BY id) AS sub_transaction_id
    FROM sub_transactions
)
-- 最后过滤日期条件
SELECT *
FROM numbered_transactions
WHERE date >= '2023-02-05' AND date <= '2023-03-05';

效果验证

查询2023-02-05至2023-03-05的记录时，返回的sub_transaction_id为2，符合需求；
查询日期大于2023-03-05的记录时，sub_transaction_id为3；
若分组内前置记录（比如id=51的记录）被删除，重新执行SQL时，剩余记录的序号会自动重置：id=52的sub_transaction_id变为1，id=53变为2。