我正在使用 oracle 運行此查詢，但我得到了著名的 oracle 錯誤 ORA-01427: single-row subquery returns more than one row
Select
DISTINCT s1.cityArrival,
s2.cityDeparture,
s2.sectionStartDate,
userFirstName
FROM
sections s1,
sections s2,
trajectory t,
userInfo
WHERE
s1.trajectId = s2.trajectId
AND t.trajectId = s1.trajectId
AND (
s1.cityArrival = 'Paris'
AND s2.cityDeparture = 'Rochelle'
)
AND driverMail = mailUser
AND (
Select
availableSeats
FROM
sections s3
WHERE
s3.trajectId = s1.trajectId
AND s2.sectionId <= s3.sectionId
AND s3.sectionId <= s1.sectionId
) not IN (0)
這是我的資料庫的架構：
I want to select the trajectories given a date and a city of arrival and city of departure having for each section an availableSeats > 0. The problem is that I cannot find a way to do this without for loop in sql and it becomes complicated.
SectionId is just the index of the enroute city in a proposed trajectory, that is why I can use it to get all the chuncks between two given destination.
NB: I made the design of the DB, if you have remarks on the design don't hesitate to tell me, Thanks in advance
uj5u.com熱心網友回復：
只要要檢查一個值 0，您就可以反轉 IN 運算元。
..
AND 0 not IN (
Select
availableSeats
FROM
sections s3
WHERE
s3.trajectId = s1.trajectId
AND s2.sectionId <= s3.sectionId
AND s3.sectionId <= s1.sectionId
)
uj5u.com熱心網友回復：
該錯誤是由 IN 子句之前的子查詢引起的。第一部分回傳結果集而不是運算式。以下是使用 IN 子句的語法：https ://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm
另一方面，您可以將 WHERE 子句中的最后一個子查詢更新為
...
AND EXISTS (SELECT 1
FROM
sections s3
WHERE
s3.trajectId = s1.trajectId
AND s2.sectionId <= s3.sectionId
AND s3.sectionId <= s1.sectionId
AND s3.availableSeats > 0
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/457823.html