mysql查询if_MySQL IF ELSEIF在select查询中

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;

解决方案

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;

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