BigQuery子查询报错：内连接运算符不匹配及表未识别

你的查询在连接子句中未明确指定字段所属的表/子查询，导致BigQuery无法解析字段来源，进而触发匹配错误。同时为了语法规范和可读性，需要对查询做以下修正：

修正后的查询代码：

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

关键修正点：

• 给citibike_stations表添加别名s，在SELECT和连接条件中明确使用s.station_id、s.name，消除字段来源歧义
• 连接条件中明确指定station_num_trips.start_station_id，让BigQuery清晰识别字段所属
• 给子查询中的COUNT(*)补充AS关键字，符合SQL语法规范

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