如何为Union查询的SQL结果添加序列号？

首先修正原SQL的拼写错误：第一个SELECT语句中的toatl_count是笔误，应改为total_count，否则UNION会因列名不一致报错。

方法一：使用窗口函数（主流数据库通用，如MySQL 8.0+、PostgreSQL、Oracle、SQL Server等） #

通过将原UNION结果作为子查询，在外层用ROW_NUMBER()窗口函数生成序列号，可灵活控制排序逻辑：

SELECT 
  ROW_NUMBER() OVER (ORDER BY sort_order) AS `serial number`,
  total_count,
  error
FROM (
  -- 子查询中加入排序字段，保证序号固定对应指定内容
  SELECT COUNT(*) AS total_count, 'employee is Invalid' AS error, 1 AS sort_order FROM emp
  UNION
  SELECT COUNT(*) AS total_count, 'company name is incorrect' AS error, 2 AS sort_order FROM company
) AS combined_results;

如果不需要固定序号顺序，仅需按统计值排序生成序号，可简化为：

SELECT 
  ROW_NUMBER() OVER (ORDER BY total_count) AS `serial number`,
  total_count,
  error
FROM (
  SELECT COUNT(*) AS total_count, 'employee is Invalid' AS error FROM emp
  UNION
  SELECT COUNT(*) AS total_count, 'company name is incorrect' AS error FROM company
) AS combined_results;

方法二：使用变量（兼容不支持窗口函数的老版本数据库，如MySQL 5.7及以前） #

通过自定义变量累加生成序列号：

SET @row_num = 0;
SELECT 
  (@row_num := @row_num + 1) AS `serial number`,
  total_count,
  error
FROM (
  SELECT COUNT(*) AS total_count, 'employee is Invalid' AS error FROM emp
  UNION
  SELECT COUNT(*) AS total_count, 'company name is incorrect' AS error FROM company
) AS combined_results
ORDER BY total_count; -- 可根据需求调整排序规则

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