mysql里面case_MySQL中的嵌套CASE语句

My first time working with CASE Logic in SQL statements. Everything works if I remove the CASE statements, so the SQL is valid without it.

I need to calculate the total item price based on a couple of things.

If "Sales Price" is active AND "Option Upcharge" has a value, the total is: Qty * (Sales Price + Option Upcharge)

If "Sales Price is inactive AND "Option Upcharge" has a value, the total is: Qty * (Price + Option Upcharge)

If "Sales Price" is active AND "Option Upcharge" has NO value, the total is: Qty * Sales Price

If "Sales Price is inactive AND "Option Upcharge" has NO value, the total is: Qty * Price

If no Option was added, the value for tblproduct_options.option_upcharge is NULL in the output.

Thanks for the help.

Brett

Here is my SQL:

SELECT tblshopping_cart.session_id, tblshopping_cart.product_id, tblshopping_cart.product_qty, tblshopping_cart.product_option, tblproducts.product_title, tblproducts.product_price, tblproducts.product_sale_price_status, tblproducts.product_sale_price, tblproduct_options.option_text, tblproduct_options.option_upcharge,

CASE

WHEN (tblproducts.product_sale_price_status = 'Y')

CASE

WHEN (tblproduct_options.option_upcharge IS NOT NULL)

THEN (tblshopping_cart.product_qty * (tblproducts.product_sale_price + tblproduct_options.option_upcharge))

ELSE (tblshopping_cart.product_qty * tblproducts.product_sale_price)

END

ELSE

CASE

WHEN (tblproduct_options.option_upchage IS NOT NULL)

THEN (tblshopping_cart.product_qty * (tblproducts.product_price + tblproduct_options.option_upcharge))

ELSE (tblshopping_cart.product_qty * tblproducts.product_price)

END

END AS product_total

FROM tblshopping_cart

INNER JOIN tblproducts ON tblshopping_cart.product_id = tblproducts.product_id

LEFT JOIN tblproduct_options ON tblshopping_cart.product_option = tblproduct_options.option_product_id

ORDER BY tblshopping_cart.product_qty ASC

It fails with with message:

CASE

WHEN (tblproduct_options.option_upcharge IS NOT NULL)

THEN (tblshopping_' at line 4

解决方案

You are missing a THEN in your first CASE Statement. (sorry I had to add table aliases)

SELECT sc.session_id

, sc.product_id

, sc.product_qty

, sc.product_option

, p.product_title

, p.product_price

, p.product_sale_price_status

, p.product_sale_price

, po.option_text

, po.option_upcharge

, CASE

WHEN (p.product_sale_price_status = 'Y')

THEN

CASE

WHEN (po.option_upcharge IS NOT NULL)

THEN (sc.product_qty * (p.product_sale_price + po.option_upcharge))

ELSE (sc.product_qty * p.product_sale_price)

END

ELSE

CASE

WHEN (po.option_upchage IS NOT NULL)

THEN (sc.product_qty * (p.product_price + po.option_upcharge))

ELSE (sc.product_qty * p.product_price)

END

END AS product_total

FROM tblshopping_cart sc

INNER JOIN tblproducts p

ON sc.product_id = p.product_id

LEFT JOIN tblproduct_options po

ON sc.product_option = po.option_product_id

ORDER BY sc.product_qty ASC