如何一次性查询各表的MAX(created_at)与MIN(created_at)值？

要批量查询所有带created_at列的表的最小、最大时间，核心思路是先从information_schema里筛选出目标表，再动态生成每个表的MIN(created_at)和MAX(created_at)查询，最后把结果合并起来。不同数据库的动态SQL写法略有差异，下面是主流数据库的实现方案：

PostgreSQL 写法 #

用string_agg拼接查询语句，再通过EXECUTE执行：

DO $$
DECLARE
  sql_query TEXT;
BEGIN
  SELECT string_agg(
    format(
      'SELECT ''%I'' AS table_name, MIN(created_at) AS min_created_at, MAX(created_at) AS max_created_at FROM %I.%I',
      table_name,
      table_schema,
      table_name
    ),
    ' UNION ALL '
  ) INTO sql_query
  FROM information_schema.columns
  WHERE TABLE_CATALOG = 'whatever'
    AND TABLE_SCHEMA = 'my_schema'
    AND COLUMN_NAME = 'created_at'
    AND DATA_TYPE IN ('timestamp', 'timestamp without time zone', 'timestamp with time zone', 'datetime');

  EXECUTE sql_query;
END $$;

MySQL 写法 #

用GROUP_CONCAT拼接，再通过预编译语句执行：

SET @sql = NULL;

SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT ''', table_name, ''' AS table_name, MIN(created_at) AS min_created_at, MAX(created_at) AS max_created_at FROM `', table_schema, '`.`', table_name, '`'
  ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE TABLE_CATALOG = 'whatever'
  AND TABLE_SCHEMA = 'my_schema'
  AND COLUMN_NAME = 'created_at'
  AND DATA_TYPE IN ('timestamp', 'datetime');

-- 如果表数量多，可能需要先调整GROUP_CONCAT长度限制
-- SET SESSION group_concat_max_len = 1000000;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 写法 #

用STRING_AGG生成查询，通过sp_executesql执行：

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
  FORMATMESSAGE(
    'SELECT ''%s'' AS table_name, MIN(created_at) AS min_created_at, MAX(created_at) AS max_created_at FROM %s.%s',
    table_name,
    QUOTENAME(table_schema),
    QUOTENAME(table_name)
  ),
  ' UNION ALL '
)
FROM information_schema.columns
WHERE TABLE_CATALOG = 'whatever'
  AND TABLE_SCHEMA = 'my_schema'
  AND COLUMN_NAME = 'created_at'
  AND DATA_TYPE IN ('datetime', 'datetime2', 'smalldatetime'); -- 注意SQL Server的timestamp是行版本号，不是时间类型，别选错

EXEC sp_executesql @sql;

注意事项 #

• 把语句里的whatever和my_schema替换成你的实际目录和 schema 名称
• 调整DATA_TYPE的筛选值，匹配你数据库里created_at列的实际类型（比如有些库用timestamp with time zone，有些用datetime2）
• 如果涉及大量表，注意动态SQL的长度限制，比如MySQL可能需要临时调大group_concat_max_len

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