mysql子查询多个条件

Question

我有一个表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作为From station和stop2作为To station，我想得到1234，但不是“stop2作为From Station”和“stop1作为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 作为起始站，stop2 作为终止站”和“stop2 作为起始站，stop1 作为终止站”的route_id

Answer 1

如果我正确理解了您的要求，您可以尝试以下操作:

SELECT
    route_id
FROM 
    bus_stops
WHERE
    stop_name IN ('stop1','stop2')
GROUP BY
    route_id
HAVING 
    COUNT(*) = 2

更新:并按照您想要的顺序往返:

SELECT
    tmp.route_id,
    GROUP_CONCAT(IF(tmp.smin = bs.sl_no AND bs.stop_name = 'stop1','from stop1','') SEPARATOR '') as bfrom,
    GROUP_CONCAT(IF(tmp.smax = bs.sl_no AND bs.stop_name = 'stop2','to stop2','') SEPARATOR '') as bto
FROM (
    SELECT
        route_id,
        MIN(sl_no) as smin,
        MAX(sl_no) as smax
    FROM 
        bus_stops
    WHERE
        stop_name IN ('stop1','stop2')
    GROUP BY
        route_id
    HAVING 
        COUNT(*) = 2
) as tmp
INNER JOIN bus_stops bs
    ON bs.route_id = tmp.route_id
GROUP BY
    tmp.route_id
HAVING
    bfrom <> ''
    AND bto <> ''