如何改写SQL语句避免子查询，获取指定条件的最大交易编号？

原查询通过标量子查询获取最大交易编号，数据量较大时可能触发两次表扫描。以下是两种兼容Oracle和SQL Server的优化方案，可按需匹配原查询逻辑：

方案1：保留原逻辑（返回所有最大交易编号的记录） #

使用DENSE_RANK()窗口函数，仅需一次表扫描即可完成计算，性能更优，且能返回所有拥有最大交易编号的记录（与原查询逻辑完全一致）：

SELECT CUST_NAME, TRANSCANTIONS_NB
FROM (
    SELECT 
        CUST_NAME, 
        TRANSCANTIONS_NB,
        DENSE_RANK() OVER (ORDER BY TRANSCANTIONS_NB DESC) AS rank_num
    FROM TRANSCANTIONS
    WHERE CODE = '477' AND STATUS IN ('1', '7')
) ranked_trans
WHERE rank_num = 1;

若你的场景中最大交易编号唯一，也可用ROW_NUMBER()替代DENSE_RANK()，效果一致。

方案2：仅返回单条最大交易编号记录 #

若只需任意一条拥有最大交易编号的记录，可使用FETCH FIRST语法（Oracle 12c+、SQL Server 2012+均支持），写法更简洁：

SELECT CUST_NAME, TRANSCANTIONS_NB
FROM TRANSCANTIONS
WHERE CODE = '477' AND STATUS IN ('1', '7')
ORDER BY TRANSCANTIONS_NB DESC
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

性能优化关键 #

为进一步提升查询速度，建议创建复合覆盖索引：

-- Oracle/SQL Server 通用语法
CREATE INDEX idx_trans_code_status_nb ON TRANSCANTIONS (CODE, STATUS, TRANSCANTIONS_NB DESC) INCLUDE (CUST_NAME);

该索引可让数据库直接从索引中获取所需数据，无需回表查询原表，大幅降低IO开销。

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