如何用SQL WHILE循环遍历test_开头的Schema并查询对应order表数据？

这个需求完全可以实现，核心是通过动态SQL遍历所有符合条件的Schema，拼接每个Schema下的统计查询并合并结果。以下是针对不同数据库的具体实现方案：

一、前置准备：获取带行号的目标Schema列表 #

首先筛选出存在order表的test_开头Schema（避免无表导致报错），同时生成对应的行号：

SELECT 
  ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA) AS rn,
  TABLE_SCHEMA AS schema_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test\_%' 
  AND TABLE_NAME = 'order'
GROUP BY TABLE_SCHEMA
ORDER BY TABLE_SCHEMA;

二、MySQL实现方案 #

方法1：用GROUP_CONCAT生成动态SQL（无需存储过程） #

SET @sql = (
  SELECT GROUP_CONCAT(
    CONCAT(
      'SELECT ', rn, ' AS ROW_NUMBER, ',
      QUOTE(schema_name), ' AS TABLE_SCHEMA, ',
      'name, COUNT(*) AS `count(''list'')` ',
      'FROM `', schema_name, '`.`order` ',
      'GROUP BY name'
    ) SEPARATOR ' UNION ALL '
  )
  FROM (
    SELECT 
      ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA) AS rn,
      TABLE_SCHEMA AS schema_name
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA LIKE 'test\_%' 
      AND TABLE_NAME = 'order'
    GROUP BY TABLE_SCHEMA
    ORDER BY TABLE_SCHEMA
  ) AS schema_list
);

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

方法2：存储过程+游标遍历 #

DELIMITER //

CREATE PROCEDURE GetAllOrderCounts()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE rn INT;
  DECLARE schema_name VARCHAR(255);
  DECLARE sql_query VARCHAR(10000) DEFAULT '';
  
  -- 定义游标获取带行号的Schema列表
  DECLARE schema_cursor CURSOR FOR
    SELECT 
      ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA) AS rn,
      TABLE_SCHEMA AS schema_name
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA LIKE 'test\_%' 
      AND TABLE_NAME = 'order'
    GROUP BY TABLE_SCHEMA
    ORDER BY TABLE_SCHEMA;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN schema_cursor;
  
  read_loop: LOOP
    FETCH schema_cursor INTO rn, schema_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    -- 拼接查询语句，用UNION ALL合并
    IF sql_query != '' THEN
      SET sql_query = CONCAT(sql_query, ' UNION ALL ');
    END IF;
    
    SET sql_query = CONCAT(
      sql_query,
      'SELECT ', rn, ' AS ROW_NUMBER, ',
      QUOTE(schema_name), ' AS TABLE_SCHEMA, ',
      'name, COUNT(*) AS `count(''list'')` ',
      'FROM `', schema_name, '`.`order` ',
      'GROUP BY name'
    );
  END LOOP;
  
  CLOSE schema_cursor;
  
  -- 执行动态SQL
  PREPARE stmt FROM sql_query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程获取结果
CALL GetAllOrderCounts();

三、SQL Server实现方案 #

DECLARE @sql NVARCHAR(MAX) = '';

-- 拼接每个Schema的统计查询
SELECT @sql += 
  'SELECT ' + CAST(rn AS NVARCHAR) + ' AS ROW_NUMBER, ' +
  '''' + schema_name + ''' AS TABLE_SCHEMA, ' +
  'name, COUNT(*) AS [count(''list'')] ' +
  'FROM [' + schema_name + '].[order] ' +
  'GROUP BY name UNION ALL '
FROM (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA) AS rn,
    TABLE_SCHEMA AS schema_name
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA LIKE 'test\_%' 
    AND TABLE_NAME = 'order'
  GROUP BY TABLE_SCHEMA
  ORDER BY TABLE_SCHEMA
) AS schema_list;

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

-- 执行动态SQL
EXEC sp_executesql @sql;

四、关键注意事项 #

• 避免无表报错：必须在筛选Schema时加上TABLE_NAME = 'order'，确保只处理存在目标表的Schema。
• 特殊字符转义：如果Schema名称包含特殊字符，要使用反引号（MySQL）或方括号（SQL Server）包裹，避免语法错误。
• 计数优化：COUNT('list')等价于COUNT(*)，后者性能更优，建议替换使用。

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