mysql - WHERE 比较器的 SQL 多重查询

Question

我正在尝试编写一个 MySQL 查询，该查询将根据对 TABLE1 的查询结果从 TABLE2 选择结果。像这样的事情:

SELECT route_id FROM trips WHERE trip_id = 
SELECT trip_id  FROM stop_times WHERE 
stop_id=<ID GOES HERE> LIMIT 1

但这不起作用，所以我尝试了这个:

SELECT route_id FROM trips WHERE trip_id 
INNER JOIN(
SELECT trip_id  FROM stop_times WHERE stop_id=<ID GOES HERE> LIMIT 1
)

而且它也不起作用。我收到此错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN(SELECT trip_id  FROM stop_times WHERE stop_id="16817" LIMIT 1)
LIMIT 0' at line 1 

Answer 1

您的原始查询应如下编写:

SELECT t.route_id
FROM trips t
WHERE t.trip_id = (SELECT st.trip_id
                   FROM stop_times st
                   WHERE st.stop_id = <ID GOES HERE>
                   LIMIT 1
                  );

我认为这通常会写成:

SELECT t.route_id
FROM stop_times st JOIN
     trips t
     ON t.trip_id = st.trip_id
WHERE st.stop_id = <ID GOES HERE>
LIMIT 1;

这假设您希望返回一行。