MySQL 中的嵌套 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11618730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nested CASE statements in MySQL
提问by Brett
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.
我第一次在 SQL 语句中使用 CASE Logic。如果我删除 CASE 语句，一切都会正常，所以没有它，SQL 是有效的。
I need to calculate the total item price based on a couple of things.
我需要根据几件事来计算商品的总价格。
If "Sales Price" is activeAND "Option Upcharge" has a value, the total is: Qty * (Sales Price + Option Upcharge)
如果“销售价格”处于活动状态且“期权加价”有值，则总数为：数量 *（销售价格 + 期权加价）
If "Sales Price is inactiveAND "Option Upcharge" has a value, the total is: Qty * (Price + Option Upcharge)
如果“销售价格处于非活动状态且“期权加价”有值，则总数为：数量 *（价格 + 期权加价）
If "Sales Price" is activeAND "Option Upcharge" has NO value, the total is: Qty * Sales Price
如果“销售价格”处于活动状态且“期权加价”没有价值，则总数为：数量 * 销售价格
If "Sales Price is inactiveAND "Option Upcharge" has NO value, the total is: Qty * Price
如果“销售价格无效且“期权加价”没有价值，则总数为：数量 * 价格
If no Option was added, the value for tblproduct_options.option_upchargeis NULL in the output.
如果未添加选项，则tblproduct_options.option_upcharge的值在输出中为 NULL。
Thanks for the help.
谢谢您的帮助。
Brett
布雷特
Here is my SQL:
这是我的 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
回答by Taryn
You are missing a THENin your first CASEStatement. (sorry I had to add table aliases)
您THEN的第一个CASEStatement中缺少 a 。（对不起，我不得不添加表别名）
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 <-- add this
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
回答by mellamokb
It looks like you're missing THENin the outer CASE:
看起来你THEN在外面失踪了CASE：
CASE
WHEN (tblproducts.product_sale_price_status = 'Y') THEN
^^^^ add this
回答by Nico
I think your problem is the way you write you case.
我认为你的问题是你写案例的方式。
You missed a THEN after your first when. You also missed a END.
在第一次之后，您错过了 THEN。你也错过了 END。
CASE
WHEN (tblproducts.product_sale_price_status = 'Y')
THEN
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
