MySQL 存储过程 : IF in WHERE

Question

我正在尝试以下列方式选择一些数据:

SELECT column
FROM table
WHERE a = a1
AND (b = b1 OR b = b2 OR b = b3);

我想让它做的是如果 b 不等于 b1，检查是否 b=b2。但是，如果 b=b1，则不检查其他条件。此 select 语句的结果必须只有一个条目。但是，在 I have no 语句中，它会检查所有三个条件，有时会返回多行。同样，我希望它停止检查条件是否为真。

关于如何实现的任何想法？我试过 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。子查询返回多于 1 行”

Answer 1

通过使用 IF() + IF() + IF()，并测试 sum = 1 可以防止很多额外的条件而不是其他条件。如果 2 或所有 3 都相同，则总和将大于 1。如果它们都不匹配，则结果为 0。这应该正是您想要的。

SELECT column
  FROM table
  WHERE a = a1
    AND  if( b = b1, 1, 0 )
       + if( b = b2, 1, 0 )
       + if( b = b3, 1, 0 ) = 1

或者...在 ypercube 的帮助下，我一直忘记逻辑测试分别为 true/false 返回 1 或 0，例如...

SELECT column
  FROM table
  WHERE a = a1
    AND  (b = b1) + (b = b2) + (b = b3) = 1