MySQL存储过程:如果在哪里。

I am trying to select some data in the following manner:

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

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

What I want it to do is if b is not equal to b1, check if b=b2. However, if b=b1, do not check other conditions. The result of this select statement must be only one entry. However, in the statement I have no, it checks all of the three conditions and sometimes returns multiple rows. Again, I would like it to stop checking if the condition is true.

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

Any ideas on how could this be implemented? I tried case but it did not work out...

关于如何实现这一点有什么想法吗?我尝试了case，但是没有成功。

Thank you in advance!

提前谢谢你!

EDIT Here is an actual query i am trying to run.

这里的编辑是我正在尝试运行的一个实际查询。

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;

Here is a sample output:

下面是一个输出示例:

airlinename         flt_no  flt_date        destination    phone         pcs   weight
Everts Air Alaska   CH1     2008-02-20      Hughes         9074502351    24    2121

The query inserts bunch of flight data into temporary table. What I am having trouble with is getting a phone number for a location. This part is as follows:

查询将飞行数据插入到临时表中。我遇到的麻烦是找个地方的电话号码。本部分内容如下:

(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

In the query advised by Amit Bhargava, I get the right result only if there is one row in the temporary table. If there are more, it throws an error in the selecting phone part.

在Amit Bhargava建议的查询中，只有在临时表中有一行时，我才能得到正确的结果。如果有更多，它会在选择电话部分中抛出一个错误。

"Error Code: 1242. Subquery returns more than 1 row"

“错误代码:1242。子查询返回多于一行"

2 个解决方案

#1

Please try the following. Not the most elegant solution, but it should work.

请尝试以下。这不是最优雅的解决方案，但它应该是可行的。

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

#2

By using IF() + IF() + IF(), and testing the sum = 1 prevents a lot of extra and not of the other criteria. If 2 or all 3 are the same, the summation will be greater than 1. If none of them match, the result is 0. This should get exactly what you want.

如果()+ 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

Or... as assisted by ypercube, and I keep forgetting logical tests return either 1 or 0 for true / false respectively, such as...

还是……在ypercube的帮助下，我不断忘记逻辑测试对于真/假分别返回1或0，例如……

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

#1