Mysql

SQL 錯誤124212421242210002100021000: 子查詢返回超過 1 行 (AMATEUR)

  • March 24, 2022

基本上,一切都在括號中自行工作。括號中的語句在不同的行中返回了 4 個不同的值。

當子查詢只能返回 1 行時,如何解決我需要將括號外的 sql 語句應用於括號中語句返回的 4 個值的事實?

SELECT 
   to2.Name, to2.PhoneNum
FROM
   tbl_operatordesc to2
WHERE
   to2.operatorID = (SELECT 
           to3.operatorID
       FROM
           tbl_operatorrouterelation to3
       WHERE
           to3.routeID = (SELECT 
                   tr.routeID
               FROM
                   tbl_route tr
               WHERE
                   tr.`Start` = (SELECT 
                           tb.busstopID
                       FROM
                           tbl_busstop tb
                       WHERE
                           tb.Description = 'Durham Estate')
                       OR tr.Destination = (SELECT 
                           tb.busstopID
                       FROM
                           tbl_busstop tb
                       WHERE
                           tb.Description = 'Durham Estate')))

謝謝!

您應該在 WHERE 子句中嘗試比較的欄位上的每個表之間加入。那麼你的陳述可以在此之上。

使用每一個 where IN 子句,所以如果你有多個元素,你就不必費心了

SELECT 
   to2.Name, to2.PhoneNum
FROM
   tbl_operatordesc to2
WHERE
   to2.operatorID IN (SELECT 
           to3.operatorID
       FROM
           tbl_operatorrouterelation to3
       WHERE
           to3.routeID IN (SELECT 
                   tr.routeID
               FROM
                   tbl_route tr
               WHERE
                   tr.`Start` IN (SELECT 
                           tb.busstopID
                       FROM
                           tbl_busstop tb
                       WHERE
                           tb.Description = 'Durham Estate')
                       OR tr.Destination IN (SELECT 
                           tb.busstopID
                       FROM
                           tbl_busstop tb
                       WHERE
                           tb.Description = 'Durham Estate')))

引用自:https://dba.stackexchange.com/questions/280768