如何用单条SQL合并100个查询并输出各查询耗时？

要把多条SELECT查询合并成单条语句并输出每条的执行耗时，核心思路是给每个子查询添加计时逻辑，再用UNION ALL拼接所有结果。下面分主流数据库给出具体实现：

MySQL #

利用SYSDATE()获取实时时间，结合TIMESTAMPDIFF计算耗时，同时给每个查询加标识区分：

SELECT 
  'employee' AS query_name,
  TIMESTAMPDIFF(MICROSECOND, @start := SYSDATE(), SYSDATE()) AS execution_time_us,
  e.*
FROM employee e
UNION ALL
SELECT 
  'employee_address' AS query_name,
  TIMESTAMPDIFF(MICROSECOND, @start := SYSDATE(), SYSDATE()) AS execution_time_us,
  ea.*
FROM employee_address ea
UNION ALL
SELECT 
  'sales' AS query_name,
  TIMESTAMPDIFF(MICROSECOND, @start := SYSDATE(), SYSDATE()) AS execution_time_us,
  s.*
FROM sales s;

• 这里用微秒（MICROSECOND）作为计时单位，可根据需求换成毫秒（MILLISECOND）或秒（SECOND）
• 变量@start用来记录每个子查询的开始时间

PostgreSQL #

PostgreSQL中now()是事务启动时间，需用clock_timestamp()获取实时时间，计算耗时用时间差提取：

SELECT 
  'employee' AS query_name,
  EXTRACT(MICROSECONDS FROM (clock_timestamp() - query_start)) AS execution_time_us,
  e.*
FROM (SELECT clock_timestamp() AS query_start) AS t, employee e
UNION ALL
SELECT 
  'employee_address' AS query_name,
  EXTRACT(MICROSECONDS FROM (clock_timestamp() - query_start)) AS execution_time_us,
  ea.*
FROM (SELECT clock_timestamp() AS query_start) AS t, employee_address ea
UNION ALL
SELECT 
  'sales' AS query_name,
  EXTRACT(MICROSECONDS FROM (clock_timestamp() - query_start)) AS execution_time_us,
  s.*
FROM (SELECT clock_timestamp() AS query_start) AS t, sales s;

• 每个子查询都单独生成query_start时间戳，确保计时准确

SQL Server #

用SYSDATETIME()获取高精度时间，DATEDIFF计算耗时：

DECLARE @start_time DATETIME2;

SELECT 
  'employee' AS query_name,
  DATEDIFF(MICROSECOND, @start_time, SYSDATETIME()) AS execution_time_us,
  e.*
FROM (SELECT @start_time = SYSDATETIME()) AS t, employee e
UNION ALL
SELECT 
  'employee_address' AS query_name,
  DATEDIFF(MICROSECOND, @start_time, SYSDATETIME()) AS execution_time_us,
  ea.*
FROM (SELECT @start_time = SYSDATETIME()) AS t, employee_address ea
UNION ALL
SELECT 
  'sales' AS query_name,
  DATEDIFF(MICROSECOND, @start_time, SYSDATETIME()) AS execution_time_us,
  s.*
FROM (SELECT @start_time = SYSDATETIME()) AS t, sales s;

关键注意事项 #

• 结果集结构统一：如果各查询返回的列数、类型不一致，需要用NULL AS 列名填充缺失列，确保UNION ALL能正常执行
• 优先用UNION ALL：比UNION高效，因为不会做去重操作，适合这种不需要去重的场景
• 计时精度选择：根据需求调整计时单位，微秒适合精细分析，秒或毫秒适合粗略统计
• 近似计时：这种方法是子查询级别的近似计时，如果需要更专业的性能分析，建议用数据库自带的性能监控工具，但如果必须合并成单条语句，上述方法完全可行

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