如何在多表数据库中查询最新创建的10条记录及所属表

MySQL 实现方式 #

步骤1：批量生成单表最新记录查询语句 #

利用系统表information_schema.tables获取所有用户数据表，自动生成每个表的最新记录查询SQL：

SELECT CONCAT(
    'SELECT ''', table_name, ''' AS table_name, * FROM ', table_name, 
    ' ORDER BY create_time DESC LIMIT 1;'
) AS query_sql
FROM information_schema.tables
WHERE table_schema = '你的数据库名' -- 替换为实际数据库名称
AND table_type = 'BASE TABLE'; -- 仅查询普通数据表，排除视图、临时表等

步骤2：合并结果并取TOP10 #

将上面生成的所有SQL用UNION ALL合并，再整体排序取最新10条记录：

SELECT * FROM (
    SELECT 'table1' AS table_name, * FROM table1 ORDER BY create_time DESC LIMIT 1
    UNION ALL
    SELECT 'table2' AS table_name, * FROM table2 ORDER BY create_time DESC LIMIT 1
    -- 此处补充其他表的查询语句，或直接用步骤1生成的内容批量替换
) AS all_latest_records
ORDER BY create_time DESC
LIMIT 10;

PostgreSQL 实现方式 #

步骤1：生成单表查询语句 #

SELECT format(
    'SELECT ''%I'' AS table_name, * FROM %I ORDER BY create_time DESC LIMIT 1;',
    table_name, table_name
) AS query_sql
FROM information_schema.tables
WHERE table_schema = 'public' -- 替换为实际schema名称
AND table_type = 'BASE TABLE';

步骤2：合并排序取TOP10 #

WITH all_latest AS (
    SELECT 'table1' AS table_name, * FROM table1 ORDER BY create_time DESC LIMIT 1
    UNION ALL
    SELECT 'table2' AS table_name, * FROM table2 ORDER BY create_time DESC LIMIT 1
)
SELECT * FROM all_latest ORDER BY create_time DESC LIMIT 10;

SQL Server 实现方式 #

步骤1：生成单表查询语句 #

SELECT 'SELECT ''' + name + ''' AS table_name, * FROM ' + QUOTENAME(name) + ' ORDER BY create_time DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;'
FROM sys.tables
WHERE type = 'U'; -- 仅查询用户自定义数据表

步骤2：合并排序取TOP10 #

SELECT * FROM (
    SELECT 'table1' AS table_name, * FROM table1 ORDER BY create_time DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
    UNION ALL
    SELECT 'table2' AS table_name, * FROM table2 ORDER BY create_time DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
) AS all_latest_records
ORDER BY create_time DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

关键注意事项 #

• 所有目标数据表必须包含记录创建时间的字段（示例中用create_time，可根据实际字段名调整），无该字段的表无法纳入查询范围
• 若字段名不统一，需在生成SQL时增加字段存在性判断逻辑
• 数据量较大的表，建议给create_time字段添加索引，避免单表查询耗时过长

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