学习INNER JOIN时遇BigQuery报错：Unrecognized name: start_station_id

你的SQL报错核心原因有两个：

• 子查询里CAST(start_station_id AS STRING)没给字段起别名，导致子查询结果集里不存在start_station_id这个字段，JOIN时自然找不到它。
• 外层查询和ORDER BY里的字段名与子查询的别名不匹配，比如子查询统计结果别名是num_of_rides，你外层写number_of_riders、ORDER BY写number_of_rides，后续也会触发字段未识别错误。

另外JOIN时明确指定字段所属表，能避免歧义。修正后的SQL代码如下：

SELECT
    citibike_stations.station_id,
    citibike_stations.name, 
    station_num_trips.num_of_rides AS number_of_riders_starting_at_station
FROM
    (SELECT
         CAST(start_station_id AS STRING) AS start_station_id, -- 给转换后的字段添加别名
         COUNT(*) AS num_of_rides
     FROM 
         `bigquery-public-data.new_york.citibike_trips`
     GROUP BY
         start_station_id
    ) AS station_num_trips
INNER JOIN
    `bigquery-public-data.new_york.citibike_stations` AS citibike_stations
          ON station_num_trips.start_station_id = citibike_stations.station_id -- 明确关联字段的表归属
ORDER BY 
    num_of_rides DESC -- 与子查询的统计字段别名保持一致

修正点说明：

1. 给子查询中转换后的字段加上AS start_station_id，让外部查询能正常引用该字段。
2. 给JOIN的两张表起短别名，明确关联字段的来源，避免字段名冲突。
3. 外层查询和排序字段与子查询的别名统一，消除字段未识别的问题。

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