MySQL中的嵌套CASE语句 [英] Nested CASE statements in MySQL

问题描述

我第一次在SQL语句中使用CASE Logic.如果删除CASE语句，一切都会正常，因此没有它，SQL是有效的.

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

如果未添加任何选项，则在输出中 tblproduct_options.option_upcharge 的值为NULL.

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

感谢您的帮助.

布雷特(Brett)

这是我的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

失败，并显示以下消息:

CASE
    WHEN (tblproduct_options.option_upcharge IS NOT NULL)
        THEN (tblshopping_' at line 4

推荐答案

您在第一个CASE语句中缺少THEN. (抱歉，我必须添加表别名)

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 <-- 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

这篇关于MySQL中的嵌套CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！