我正在尝试以以下方式选择一些数据:
SELECT column
FROM table
WHERE a = a1
AND (b = b1 OR b = b2 OR b = b3);
我希望它能做的是如果b不等于b1,检查b是否等于b2。但是,如果b=b1,则不要检查其他条件。 这个选择语句的结果必须只有一个条目。但是,在我没有的语句中,它检查所有三个条件,并且有时返回多行。同样,如果条件为真,我希望它停止检查。
您有任何实现这个的想法吗?我尝试了case但并没有成功......
预先感谢!
编辑 这是我正在尝试运行的实际查询。
INSERT INTO shipment_flights
(airlinename, flt_no, flt_date, destination, phone, depttime, arrivaltime, pcs, weight)
SELECT st.airlinename, flightno, flightdate, destination,
(SELECT phone
FROM carrierlocations
WHERE carriers_carrierid = (select carrierid from carriers where airlinename = st.airlinename)
AND (city = destination OR (city != destination AND
city = (SELECT city FROM airports WHERE iataid =
(SELECT airports_iataid FROM ratelegs
WHERE shipments_shipid = c.shipments_shipid))
))) phone,
depttime, arrivaltime, sum(linepcs), sum(lineweight)
FROM segment_times st
JOIN contents2flights c2f
ON st.flightid = c2f.segments_flights_flightid
AND st.segmentid = c2f.segments_segmentid
JOIN contents c
ON c.lineno = c2f.contents_lineno
AND c.shipments_shipid = c2f.contents_shipments_shipid
WHERE c.shipments_shipid = var_shipid
GROUP BY flightid
ORDER BY flightdate, depttime;
这是一个样例输出:
airlinename flt_no flt_date destination phone pcs weight
Everts Air Alaska CH1 2008-02-20 Hughes 9074502351 24 2121
这个查询将大量航班数据插入临时表。我遇到的问题是获取位置的电话号码。具体代码如下:
(SELECT phone
FROM carrierlocations
WHERE carriers_carrierid = (select carrierid from carriers where airlinename = st.airlinename)
AND (city = destination OR (city != destination AND
city = (SELECT city FROM airports WHERE iataid =
(SELECT airports_iataid FROM ratelegs
WHERE shipments_shipid = c.shipments_shipid))))) phone
在Amit Bhargava建议的查询中,只有临时表中有一行时才会得到正确的结果。如果有更多的行,则在选择电话部分会抛出错误。
"错误代码:1242。子查询返回多个行"