使用子查询和INNER JOIN时混淆表

Question

我正在尝试打印来自不同车站的ID和名称以及该车站的乘车总数。

ID和名称来自stations表，而旅行次数来自trips表，因此我创建了如下形式的查询:

SELECT id, name and num_rides
FROM (SELECT COUNT (*) num_rides
FROM tableB AS b) AS num_rides
INNER JOIN tableA AS a ON  a.station_id = b.start_station_id

问题是外部查询的JOIN语句不识别我的表的b别名，我在内部查询中别名。

我试着分别运行查询，它们都工作得很好。

为什么计算机在外部查询时不记住我的内部查询别名?

错误:Unrecognized name: trips, trip是我为表b使用的别名。

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

Answer 1

我认为问题是"旅行"Alias仅在括号内有效。尝试命名整个select语句并引用该名称。

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

Answer 2

SELECT 
station_id, /*from table: citibike_stations */
name, /*from table: citibike_trips */
number_of_rides AS number_of_rides_starting_at_station /*from table: station_num_trips*/
FROM
(SELECT  
start_station_id,
COUNT(*) 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` 
ON station_id = start_station_id
ORDER BY number_of_rides DESC