如何在BigQuery中对比多表并将查询结果按独立列展示？

要实现按red和blue各返回一行，同时展示三个表对应字段的需求，你需要用条件聚合或BigQuery的PIVOT功能，而不是单纯的UNION ALL或INNER JOIN，原因是：

• UNION ALL是合并行，会生成多条记录而非按color分组的两行；
• INNER JOIN会过滤掉任一表中无对应color的记录，导致结果行数不足。

方法1：条件聚合（兼容性强） #

先合并三个表的数据并标记表名，再按color分组，用条件判断提取各表的字段值：

WITH combined_data AS (
  SELECT 'Luxury' AS table_name, color, price, size FROM `your_dataset.Luxury` WHERE color IN ('red', 'blue')
  UNION ALL
  SELECT 'Economy' AS table_name, color, price, size FROM `your_dataset.Economy` WHERE color IN ('red', 'blue')
  UNION ALL
  SELECT 'SUVs' AS table_name, color, price, size FROM `your_dataset.SUVs` WHERE color IN ('red', 'blue')
)
SELECT
  color,
  MAX(IF(table_name = 'Luxury', price, NULL)) AS luxury_price,
  MAX(IF(table_name = 'Luxury', size, NULL)) AS luxury_size,
  MAX(IF(table_name = 'Economy', price, NULL)) AS economy_price,
  MAX(IF(table_name = 'Economy', size, NULL)) AS economy_size,
  MAX(IF(table_name = 'SUVs', price, NULL)) AS suvs_price,
  MAX(IF(table_name = 'SUVs', size, NULL)) AS suvs_size
FROM combined_data
GROUP BY color
ORDER BY color;

如果某个表没有对应color的记录，对应列会显示NULL，不影响整体结果行数。

方法2：BigQuery原生PIVOT（更简洁） #

利用BigQuery的PIVOT语法直接转置表字段：

WITH combined_data AS (
  SELECT 'Luxury' AS table_name, color, price, size FROM `your_dataset.Luxury` WHERE color IN ('red', 'blue')
  UNION ALL
  SELECT 'Economy' AS table_name, color, price, size FROM `your_dataset.Economy` WHERE color IN ('red', 'blue')
  UNION ALL
  SELECT 'SUVs' AS table_name, color, price, size FROM `your_dataset.SUVs` WHERE color IN ('red', 'blue')
)
SELECT *
FROM combined_data
PIVOT (
  MAX(price) AS price, MAX(size) AS size
  FOR table_name IN ('Luxury' AS luxury, 'Economy' AS economy, 'SUVs' AS suvs)
)
ORDER BY color;

该查询会自动按color分组，生成luxury_price、luxury_size等列，结果同样是red和blue两行。

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