聚合内用WHERE条件？高效获取物料交易日期的SQL优化咨询

你的原方案会对百万行的Translog表做两次全表扫描（每个CTE一次），这是性能瓶颈所在。更高效的方式是通过一次扫描完成两个聚合计算，用条件聚合来实现：

WITH TransDates AS (
    SELECT
        PartID,
        MIN(CASE WHEN TransDesc = 'NEW ITEM' THEN TransDate END) AS MinTrans,
        MAX(TransDate) AS MaxTrans
    FROM Translog
    GROUP BY PartID
)
SELECT
    it.PartID,
    td.MinTrans,
    td.MaxTrans
FROM ItemTab it
LEFT JOIN TransDates td 
    ON td.PartID = it.PartID

为什么这个方案更快？ #

• 只需要扫描Translog表一次，大幅减少磁盘IO开销（百万行的表，两次和一次扫描的差距非常明显）
• 条件聚合MIN(CASE...)只会对符合TransDesc = 'NEW ITEM'的行计算最小日期，和原逻辑完全一致

额外性能优化建议 #

如果Translog表的查询还是慢，可以考虑创建复合索引：

CREATE INDEX idx_translog_part_desc_date ON Translog (PartID, TransDesc, TransDate);

或者如果你的数据库支持覆盖索引（比如SQL Server的INCLUDE、MySQL的覆盖索引），可以调整为：

-- SQL Server 示例
CREATE INDEX idx_translog_part_include ON Translog (PartID) INCLUDE (TransDesc, TransDate);

这样聚合查询可以直接从索引获取数据，不需要回表查询原数据，进一步提升性能。

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