BigQuery表列名引用异常：设置别名后无法获取实际字段值

问题根源解析 #

1. 反引号使用错误导致返回字符串
   你把customer_flight_data.Loyalty_Number整个包裹在反引号中时，BigQuery会将其识别为字符串字面量，而非对表中列的引用，所以返回的结果自然是这个字符串文本，不是列的实际数值。

2. 未设置表别名导致报错
   直接写customer_flight_data.Loyalty_Number时出现“Unrecognized name: customer_flight_data”错误，是因为你没有给FROM子句中的表设置别名，BigQuery无法识别这个未定义的标识符。

正确写法示例 #

1. 单表查询的正确别名设置

给表设置别名后，通过别名引用列即可：

SELECT t1.Loyalty_Number AS loyalty_number1
FROM `case-study-airline-69374.customer_flight_activity.customer_flight_data` AS t1

如果你的列名确实是带空格的Loyalty Number（而非下划线），需要单独给列名加反引号：

SELECT t1.`Loyalty Number` AS loyalty_number1
FROM `case-study-airline-69374.customer_flight_activity.customer_flight_data` AS t1

2. 基于Loyalty列的LEFT JOIN完整实现

假设另一张表为customer_info，同样包含同名列，完整的LEFT JOIN写法如下：

SELECT
  t1.Loyalty_Number AS loyalty_number1,
  t2.customer_name,
  t2.contact_email
FROM `case-study-airline-69374.customer_flight_activity.customer_flight_data` AS t1
LEFT JOIN `case-study-airline-69374.customer_flight_activity.customer_info` AS t2
  ON t1.Loyalty_Number = t2.Loyalty_Number

若列名带空格，调整为：

SELECT
  t1.`Loyalty Number` AS loyalty_number1,
  t2.customer_name,
  t2.contact_email
FROM `case-study-airline-69374.customer_flight_activity.customer_flight_data` AS t1
LEFT JOIN `case-study-airline-69374.customer_flight_activity.customer_info` AS t2
  ON t1.`Loyalty Number` = t2.`Loyalty Number`

关键注意事项 #

• 带特殊字符（空格、符号等）的列名，必须单独用反引号包裹，不能将表名+列名一起包裹。
• 给表设置别名是BigQuery中多表查询的标准操作，既能简化代码，也能避免列名歧义或识别错误。

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