使用别名与子查询时遭遇Unrecognized Name Error的技术问询

你的SQL触发Unrecognized Name Error，是因为**trips别名仅在子查询内部生效**，外部的INNER JOIN语句无法识别这个别名。子查询已经被命名为station_num_trips，关联条件里得用这个别名来访问子查询输出的start_station_id字段。

修正后的SQL语句 #

SELECT 
    stations.station_id,
    stations.name,
    number_of_rides AS number_of_rides_starting_at_station
FROM
    (SELECT 
         start_station_id, 
         COUNT(*) number_of_rides
     FROM
         `bigquery-public-data.new_york_citibike.citibike_trips` AS trips 
     GROUP BY
         start_station_id) AS station_num_trips
INNER JOIN
    `bigquery-public-data.new_york_citibike.citibike_stations` AS stations
      ON station_num_trips.start_station_id = stations.station_id 
ORDER BY 
    number_of_rides DESC

关键修改点 #

• 把关联条件里的 trips.start_station_id 替换成 station_num_trips.start_station_id，让外部查询能正确引用子查询的字段。

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