mysql - 乘以 'msql case' 的查询结果

Question

我可以从以下查询中获得以下输出-

SELECT
    (
        CASE
        WHEN ta.Battery = 'HVA' THEN
            'Huawei Valued Battery'
        WHEN ta.Battery = 'HNVA' THEN
            'Canibalized Battery'
        WHEN ta.Battery = 'ZVA' THEN
            'ZTE Valued Battery'
        WHEN ta.Battery = 'ZNVA' THEN
            'ZTE Canibalized Battery'
        END
    ) AS Accessory_category,
    COUNT(ta.Battery) Battery_count
FROM
    tsc_accessory_details_for_repaired_and_qc_pass_phone_list ta
GROUP BY
    ta.Battery,
    ta.Battery

所以，我只需要在结果表中添加另一列，命名为“成本”。是否有可能通过修改上述查询来获取输出。例如:如果“HVA”的单位成本为 100，则总成本为 700。如果“ZVA”的单位成本为 200，则 3 个项目的总成本为 600。请提出修改建议

我需要以下输出-

谢谢查马斯

Answer 1

您可以为此添加另一个CASE，但您也可以这样做:

SELECT
    sub.Value AS Accessory_category,
    SUM(COALESCE(ta.UnitCost, sub.UnitCostValue, 0)) AS UnitCost,
    COUNT(ta.Battery) Battery_count
FROM
    tsc_accessory_details_for_repaired_and_qc_pass_phone_list ta
LEFT JOIN
(
    SELECT 'HVA' Battery, 'Huawei Valued Battery' Value, 100 UnitCose, 700 AS UnitCostValue
    UNION 
    SELECT 'HNVA', 'Canibalized Battery', 200, 600
    UNION 
    SELECT 'ZVA', 'ZTE Valued Battery', 0, 0
    UNION
    SELECT 'ZNVA', 'ZTE Canibalized Battery', 0, 0
) AS sub ON ta.Battery = sub.Battery AND ta.unitCost = sub.UnitCost
GROUP BY
    ta.Battery;

因此，您可以在子查询中列出所有值及其匹配项，然后基于它进行连接，而不是这些 case 表达式。