如何在T-SQL中按多条件筛选并保留最新创建时间的行？

需求：从数据表中筛选唯一行，当batch_no、batch_date、accountno、locationid、amount、deptid、glenkey这7个字段值完全相同时，保留whencreated字段值最新的行。

示例数据 #

-- 示例数据查询
select * 
into #temp
from 
(values 
(56555,  '2022-04-01',  48570,  111, 445.00, 217, 1877885, '2022-03-01'),
(45698,  '2022-03-01',  62550,  110, 344.59, 216, 1910945, '2022-02-01'),
(45698,  '2022-03-01',  62550,  110, 344.59, 216, 1910945, '2022-01-01')
)
t1
(batch_no, batch_date, accountno, locationid, amount, deptid, glenkey, whencreated)

期望结果 #

-- 期望结果
select * 
INTO #temp1
from 
(values 
(56555,  '2022-04-01',  48570,  111, 445.00, 217, 1877885, '2022-03-01'),
(45698,  '2022-03-01',  62550,  110, 344.59, 216, 1910945, '2022-02-01')
)
t1
(batch_no, batch_date, accountno, locationid, amount, deptid, glenkey, whencreated)

疑问：是否需要使用子查询？该如何编写对应的T-SQL语句？

是否需要子查询？ #

不一定必须用子查询，T-SQL中有多种实现方式，窗口函数是更简洁高效的选择，当然也可以用子查询或关联查询来实现。

方法一：使用ROW_NUMBER()窗口函数（推荐） #

这种方法通过窗口函数分组并排序，仅用一层子查询生成行号后筛选，逻辑清晰且高效：

SELECT batch_no, batch_date, accountno, locationid, amount, deptid, glenkey, whencreated
INTO #temp1
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY batch_no, batch_date, accountno, locationid, amount, deptid, glenkey
               ORDER BY whencreated DESC
           ) AS rn
    FROM #temp
) t
WHERE rn = 1;

方法二：使用关联子查询 #

如果偏好子查询写法，可先分组获取每组最新时间，再关联原表匹配对应行：

SELECT t1.*
INTO #temp1
FROM #temp t1
INNER JOIN (
    SELECT batch_no, batch_date, accountno, locationid, amount, deptid, glenkey,
           MAX(whencreated) AS latest_whencreated
    FROM #temp
    GROUP BY batch_no, batch_date, accountno, locationid, amount, deptid, glenkey
) t2 ON t1.batch_no = t2.batch_no
    AND t1.batch_date = t2.batch_date
    AND t1.accountno = t2.accountno
    AND t1.locationid = t2.locationid
    AND t1.amount = t2.amount
    AND t1.deptid = t2.deptid
    AND t1.glenkey = t2.glenkey
    AND t1.whencreated = t2.latest_whencreated;

方法三：使用TOP 1 WITH TIES（无显式子查询） #

这种写法无需显式子查询，直接在主查询中完成筛选，代码最简洁：

SELECT TOP 1 WITH TIES *
INTO #temp1
FROM #temp
ORDER BY ROW_NUMBER() OVER (
    PARTITION BY batch_no, batch_date, accountno, locationid, amount, deptid, glenkey
    ORDER BY whencreated DESC
);

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