如何在BigQuery的SELECT *语句中批量给字段名添加前缀/后缀

在多表查询场景中，手动给每个字段逐个添加前缀/后缀（避免字段名冲突）非常繁琐。比如原查询：

WITH a AS (
  SELECT 'value_in_table_a' AS val1, 'another' AS val2
),
b AS (
    SELECT 'value_in_table_b' AS val
)
SELECT
  a.*,
  b.*
FROM a
CROSS JOIN b

我们希望自动生成带前缀/后缀的字段名（如a_val1、val_b），无需手动编写a.val1 AS a_val1这类重复语句，同时避免返回STRUCT类型结果。

方法1：动态SQL自动生成字段列表 #

不同SQL引擎的动态语法略有差异，以下是主流引擎的实现方式：

BigQuery 实现 #

利用EXECUTE IMMEDIATE结合JSON解析获取字段名，自动拼接带前缀/后缀的字段：

DECLARE a_cols STRING;
DECLARE b_cols STRING;

-- 生成a表带前缀的字段列表
SET a_cols = (
  SELECT STRING_AGG(CONCAT('a.', column_name, ' AS a_', column_name), ', ')
  FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING((SELECT AS STRUCT 'value_in_table_a' AS val1, 'another' AS val2)), r'"(\w+)":')) AS column_name
);

-- 生成b表带后缀的字段列表
SET b_cols = (
  SELECT STRING_AGG(CONCAT('b.', column_name, ' AS ', column_name, '_b'), ', ')
  FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING((SELECT AS STRUCT 'value_in_table_b' AS val)), r'"(\w+)":')) AS column_name
);

-- 执行动态查询
EXECUTE IMMEDIATE CONCAT('
  WITH a AS (
    SELECT ''value_in_table_a'' AS val1, ''another'' AS val2
  ),
  b AS (
      SELECT ''value_in_table_b'' AS val
  )
  SELECT ', a_cols, ', ', b_cols, '
  FROM a
  CROSS JOIN b
');

PostgreSQL 实现 #

通过临时表+元数据查询生成字段列表，再执行动态SQL：

DO $$
DECLARE
  a_cols text;
  b_cols text;
BEGIN
  -- 创建临时表模拟CTE结构
  CREATE TEMP TABLE temp_a AS SELECT 'value_in_table_a' AS val1, 'another' AS val2;
  CREATE TEMP TABLE temp_b AS SELECT 'value_in_table_b' AS val;

  -- 生成a表带前缀的字段列表
  SELECT string_agg('temp_a.' || quote_ident(column_name) || ' AS a_' || quote_ident(column_name), ', ')
  INTO a_cols
  FROM information_schema.columns
  WHERE table_name = 'temp_a';

  -- 生成b表带后缀的字段列表
  SELECT string_agg('temp_b.' || quote_ident(column_name) || ' AS ' || quote_ident(column_name) || '_b', ', ')
  INTO b_cols
  FROM information_schema.columns
  WHERE table_name = 'temp_b';

  -- 执行动态查询
  EXECUTE format('SELECT %s, %s FROM temp_a CROSS JOIN temp_b', a_cols, b_cols);
END $$;

方法2：IDE正则替换快速生成 #

如果是一次性操作，用SQL IDE（如DataGrip、DBeaver）的正则替换功能可快速完成：

1. 将a.*/b.*展开为所有字段（IDE通常支持右键展开，比如DataGrip用Alt+Enter选择"Expand all columns"）
2. 用正则批量替换：
   • 给a表字段加前缀：查找a\.(\w+)，替换为a.$1 AS a_$1
   • 给b表字段加后缀：查找b\.(\w+)，替换为b.$1 AS $1_b

方法3：元数据查询生成字段清单 #

直接查询系统元数据表，生成带别名的字段列表，复制到原查询中使用：
以MySQL为例：

SELECT CONCAT('`a`.`', column_name, '` AS `a_', column_name, '`')
FROM information_schema.columns
WHERE table_schema = 'your_db' AND table_name = 'a';

将查询结果拼接成逗号分隔的字符串，替换原查询中的a.*即可。

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