我有一个表bus_stops
,其中包含带有公共汽车站的公交车的路线id,如下所示:
sl_no route_id stop_name
-------------------------------------------------------------
1 1234 stop1
2 1234 stop2
3 1235 stop7
4 1235 stop8
5 5678 stop1
6 5678 stop2
我想要一个同时包含stop1和stop2的route_id (对于stop1 as From station和stop2 as to station,我希望得到1234,但不是"stop2 as From Staion“和"stop1 as To Station")。
我写了这个查询:
SELECT DISTINCT route_id FROM bus_stops AS myAlias
WHERE EXISTS ( SELECT * FROM bus_stops WHERE route_id = myAlias.route_id AND stops = 'stop1' )
AND EXISTS ( SELECT * FROM bus_stops WHERE route_id = myAlias.route_id AND stops = 'stop2' )
但是它返回"stop1 as From station和stop2 as To station“和"stop2 as From Staion和stop1 as To Station”的route_id。
发布于 2013-06-20 16:19:56
使用JOIN来获取起始停靠点之后的路线(例如,终止停靠点的sl_no大于起始停靠点的sl_no):
SELECT a.route_id, a.stop_name, b.stop_name
FROM bus_stops a
INNER JOIN bus_stops b
ON a.route_id = b.route_id
AND a.stop_name = 'stop1'
AND b.stop_name = 'stop2'
AND a.sl_no < b.sl_no
https://stackoverflow.com/questions/17208099
复制相似问题