如何在指定SQL查询中添加行计数，是否需单独查询？

现有如下SQL查询语句：

SELECT DISTINCT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, 
 ( 
  SELECT DISTINCT p.CONSTRAINT_TYPE 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ON p.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME AND p.CONSTRAINT_TYPE='PRIMARY KEY' 
  WHERE kcu.TABLE_NAME = c.TABLE_NAME AND kcu.COLUMN_NAME=c.COLUMN_NAME 
 ) AS CONSTRAINT_TYPE, 
NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
FROM INFORMATION_SCHEMA.COLUMNS c

请问是否可在该查询中添加行计数，还是需单独创建查询？另外，是否有方法从该查询中同时获取行数？

回答 #

没问题，这两个需求都可以在现有查询的基础上实现，不需要单独创建新查询，具体方案如下：

1. 添加行计数（逐行序号） #

你可以直接用**窗口函数ROW_NUMBER()**给结果集中的每一行分配唯一序号，不需要额外写独立查询。

直接修改版（兼容多数现代数据库）

SELECT 
  ROW_NUMBER() OVER (ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME) AS ROW_NUM,
  c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, 
  ( 
   SELECT DISTINCT p.CONSTRAINT_TYPE 
   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ON p.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME AND p.CONSTRAINT_TYPE='PRIMARY KEY' 
   WHERE kcu.TABLE_NAME = c.TABLE_NAME AND kcu.COLUMN_NAME=c.COLUMN_NAME 
  ) AS CONSTRAINT_TYPE, 
  NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
FROM INFORMATION_SCHEMA.COLUMNS c
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, CONSTRAINT_TYPE

（注：这里把DISTINCT换成了GROUP BY，避免和ROW_NUMBER()的冲突，效果和原查询完全一致）

子查询包装版（适配严格SQL方言）

如果你的数据库不允许ROW_NUMBER()和DISTINCT直接混用，把原查询包装成子查询再在外层加行计数即可：

SELECT 
  ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS ROW_NUM,
  *
FROM (
  SELECT DISTINCT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, 
  ( 
   SELECT DISTINCT p.CONSTRAINT_TYPE 
   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ON p.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME AND p.CONSTRAINT_TYPE='PRIMARY KEY' 
   WHERE kcu.TABLE_NAME = c.TABLE_NAME AND kcu.COLUMN_NAME=c.COLUMN_NAME 
  ) AS CONSTRAINT_TYPE, 
  NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
  FROM INFORMATION_SCHEMA.COLUMNS c
) AS column_details

2. 同时获取总行数 #

如果想要在每一行都显示整个结果集的总行数，可以用窗口函数COUNT(*) OVER ()，这样不需要额外的联合查询或者单独统计。

示例代码（子查询包装版，避免冲突）：

SELECT 
  ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS ROW_NUM,
  COUNT(*) OVER () AS TOTAL_ROWS,
  *
FROM (
  SELECT DISTINCT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, 
  ( 
   SELECT DISTINCT p.CONSTRAINT_TYPE 
   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ON p.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME AND p.CONSTRAINT_TYPE='PRIMARY KEY' 
   WHERE kcu.TABLE_NAME = c.TABLE_NAME AND kcu.COLUMN_NAME=c.COLUMN_NAME 
  ) AS CONSTRAINT_TYPE, 
  NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
  FROM INFORMATION_SCHEMA.COLUMNS c
) AS column_details

补充说明 #

• 上述窗口函数（ROW_NUMBER()、COUNT(*) OVER ()）支持大多数现代数据库：MySQL 8.0+、PostgreSQL、SQL Server、Oracle等。
• 如果你使用的是旧版本数据库（比如MySQL 5.x），可以用用户变量实现行计数，或者单独查询总行数后用CROSS JOIN合并到结果中，但优先推荐窗口函数方案，更简洁高效。

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