MySQL IF ELSEIF在select查询中 [英] MySQL IF ELSEIF in select query

问题描述

我正在尝试根据用户选择的数量选择不同的产品价格。
这是我正在处理的查询（它有语法错误）：

I'm trying to select different prices of a product based on the quantity that user chooses. This is the query I'm working on (it has a syntax error):

 select id, 
    (SELECT 
    IF(qty_1<='23',price,1)
    ELSEIF(('23'>qty_1 && qty_2<='23'),price_2,1)
    ELSEIF(('23'>qty_2 && qty_3<='23'),price_3,1)
    ELSEIF('23'>qty_3,price_4,1)
    END IF) as total 
 from product;

推荐答案

您拥有存储过程中使用的内容这样的供参考，但它们并不打算像你现在一样使用。您可以使用 IF ，如 duskwuff 所示。但是对于眼睛来说， Case 语句更好。像这样：

You have what you have used in stored procedures like this for reference, but they are not intended to be used as you have now. You can use IF as shown by duskwuff. But a Case statement is better for eyes. Like this:

select id, 
    (
    CASE 
        WHEN qty_1 <= '23' THEN price
        WHEN '23' > qty_1 && qty_2 <= '23' THEN price_2
        WHEN '23' > qty_2 && qty_3 <= '23' THEN price_3
        WHEN '23' > qty_3 THEN price_4
        ELSE 1
    END) AS total
 from product;

这看起来更干净。我想你不需要内部 SELECT ..

This looks cleaner. I suppose you do not require the inner SELECT anyway..

这篇关于MySQL IF ELSEIF在select查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！