如何在SQL SELECT查询中替换record_dt为子查询返回的最新对应值

最优实现方案 #

你使用的是SQL Server（代码中的dbo前缀可判断），优先推荐APPLY运算符实现，性能更优、可读性更高：

SELECT r.record_id,
       r.part_id,
       dt.latest_record_dt AS record_dt, -- 替换原有r.record_dt
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id
       -- 新增关联获取每个part_id对应符合条件的最新record_dt
       OUTER APPLY (
           SELECT TOP 1 RECORD_DT AS latest_record_dt
           FROM synfact_record 
           WHERE part_id = r.part_id 
             AND PROCESS_STEP_LIST_ID IN (21,22,23)
           ORDER BY RECORD_DT DESC
       ) dt
WHERE  ( r.part_id IN (SELECT part_id
                       FROM   dbo.synfact_record AS synfact_record_1
                       WHERE  ( record_status = 1 )
                              AND ( record_type = 0 )) )
       AND ( r.product_id IN ( 38, 39, 40, 41,
                               42, 43, 44, 45,
                               46, 37, 47, 48,
                               49, 50, 51, 52,
                               53, 54, 58, 59 ) )
       AND ( r.record_id > 499 )
       AND ( r.record_status = 1 ) 

方案说明 #

1. 新增的OUTER APPLY子句通过part_id关联主查询的记录，筛选出流程步骤ID为21、22、23的记录，取时间最新的一条返回。
2. 如果你需要过滤掉没有匹配到对应record_dt的记录，将OUTER APPLY改为CROSS APPLY即可，效果等同于内连接。

替代方案（标量子查询写法） #

如果你习惯直接在字段列表写子查询，也可以用如下写法，效果完全一致：

SELECT r.record_id,
       r.part_id,
       -- 替换原有r.record_dt
       (SELECT MAX(RECORD_DT) FROM synfact_record WHERE part_id = r.part_id AND PROCESS_STEP_LIST_ID IN (21,22,23)) AS record_dt,
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id
WHERE  ( r.part_id IN (SELECT part_id
                       FROM   dbo.synfact_record AS synfact_record_1
                       WHERE  ( record_status = 1 )
                              AND ( record_type = 0 )) )
       AND ( r.product_id IN ( 38, 39, 40, 41,
                               42, 43, 44, 45,
                               46, 37, 47, 48,
                               49, 50, 51, 52,
                               53, 54, 58, 59 ) )
       AND ( r.record_id > 499 )
       AND ( r.record_status = 1 ) 

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