如何将多表中timestamp列的最后一行值合并至单个表？

问题背景 #

数据库中存在多个结构相似的表，均包含UniqueID（唯一标识列）、timestamp（时间戳列）及其他业务列，示例表结构如下：

表1示例 #

表2示例 #

需求目标 #

提取每个表中按UniqueID倒序排列的第一行的timestamp值（即表的最后更新时间），并与对应表名整合为一张结果表，要求结果可读、易于扩展（新增表时改动最小）、支持排序。

单表提取最后时间戳的基础写法如下：

SELECT
    TOP 1 timestamp
FROM [Table 1]
ORDER BY UniqueID DESC

现有实现的问题 #

已有的实现代码通过CTE结合UNION ALL实现需求，但代码冗余、可读性差，新增表时需要修改多处（新增CTE、新增UNION ALL分支），维护成本高：

WITH #CTE_Table1 AS (
    SELECT 'Table 1' AS [Table Name], timestamp, ROW_NUMBER() OVER (ORDER BY UniqueID DESC) AS RowNum
    FROM [Table 1]
),
#CTE_Table2 AS (
    SELECT 'Table 2' AS [Table Name], timestamp, ROW_NUMBER() OVER (ORDER BY UniqueID DESC) AS RowNum
    FROM [Table 2]
),
#CTE_Table3 AS (
    SELECT 'Table 3' AS [Table Name], timestamp, ROW_NUMBER() OVER (ORDER BY UniqueID DESC) AS RowNum
    FROM [Table 3]
),
#CTE_Table4 AS (
    SELECT 'Table 4' AS [Table Name], timestamp, ROW_NUMBER() OVER (ORDER BY UniqueID DESC) AS RowNum
    FROM [Table 4]
),
#CTE_Table5 AS (
    SELECT 'Table 5' AS [Table Name], timestamp, ROW_NUMBER() OVER (ORDER BY UniqueID DESC) AS RowNum
    FROM [Table 5]
)

SELECT [Table Name], [Last Timestamp]
FROM (
    SELECT [Table Name], timestamp AS [Last Timestamp], RowNum
    FROM #CTE_Table1
    WHERE RowNum = 1

    UNION ALL

    SELECT [Table Name], timestamp AS [Last Timestamp], RowNum
    FROM #CTE_Table2
    WHERE RowNum = 1

    UNION ALL

    SELECT [Table Name], timestamp AS [Last Timestamp], RowNum
    FROM #CTE_Table3
    WHERE RowNum = 1

    UNION ALL

    SELECT [Table Name], timestamp AS [Last Timestamp], RowNum
    FROM #CTE_Table4
    WHERE RowNum = 1

    UNION ALL

    SELECT [Table Name], timestamp AS [Last Timestamp], RowNum
    FROM #CTE_Table5
    WHERE RowNum = 1
) AS [Last Timestamp];

优化方案 #

方案1：简化静态SQL（适合表数量较少的场景） #

直接对每个表使用TOP 1查询并指定表名，通过UNION ALL合并结果，代码简洁易读，新增表时仅需追加一段UNION ALL子查询：

SELECT 'Table 1' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
FROM [Table 1]
ORDER BY UniqueID DESC

UNION ALL

SELECT 'Table 2' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
FROM [Table 2]
ORDER BY UniqueID DESC

UNION ALL

SELECT 'Table 3' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
FROM [Table 3]
ORDER BY UniqueID DESC

-- 新增表时，在此处继续追加UNION ALL对应的子查询即可

方案2：动态SQL（适合表数量较多或需频繁新增表的场景） #

利用系统视图自动获取目标表名，动态生成查询语句，无需手动维护每个表的查询分支，扩展性极强：

DECLARE @SQL NVARCHAR(MAX) = ''

-- 从系统视图中筛选目标表，可根据表名规则或schema调整WHERE条件
SELECT @SQL = @SQL + '
SELECT ''' + TABLE_NAME + ''' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
ORDER BY UniqueID DESC
UNION ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('Table 1', 'Table 2', 'Table 3', 'Table 4', 'Table 5')
-- 若目标表有统一命名规则，可替换为 TABLE_NAME LIKE 'Prefix_%' 之类的筛选条件

-- 移除最后一段多余的UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10)

-- 执行动态生成的SQL
EXEC sp_executesql @SQL

结果排序 #

如需对结果按时间戳或表名排序，只需在最终查询外层添加ORDER BY即可，例如：

-- 针对方案1的排序示例
SELECT *
FROM (
    SELECT 'Table 1' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
    FROM [Table 1]
    ORDER BY UniqueID DESC

    UNION ALL

    SELECT 'Table 2' AS [Table Name], TOP 1 timestamp AS [Last Timestamp]
    FROM [Table 2]
    ORDER BY UniqueID DESC
) AS Result
ORDER BY [Last Timestamp] DESC; -- 按最后更新时间倒序，或按[Table Name]排序

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