mysql - 在 then 之后使用 MySQL 别名和 case 语句

Question

我无法在 CASE 语句中使用 MySQL 别名，它会给出如下语法错误:

SELECT IFNULL(min(mFR.date),'') AS `firstDate`,
    CASE `data` 
        WHEN 'Sales'         THEN IFNULL(SUM(qty),0) AS `sales`
        WHEN 'Return'        THEN IFNULL(SUM(qty),0) AS `return`
        WHEN 'Receiving In'  THEN IFNULL(SUM(qty),0) AS `receiving_in`
        WHEN 'Receiving Out' THEN IFNULL(SUM(qty),0) AS `receiving_out`
        WHEN 'Stock'         THEN IFNULL(SUM(qty),0) AS `stock`
    END
FROM  `full_report`

它给了我([AS sales] 附近的语法错误)。

有什么建议吗？

Answer 1

您正在尝试对案例的每个分支使用别名，这是不可能的。

我认为你想使用单独的情况:

SELECT IFNULL(min(mFR.date),'') AS `firstDate`
    ,sum(CASE `data` WHEN 'Sales'         THEN qty END) AS `sales`
    ,sum(CASE `data` WHEN 'Return'        THEN qty END) AS `return`
    ,sum(CASE `data` WHEN 'Receiving In'  THEN qty END) AS `receiving_in`
    ,sum(CASE `data` WHEN 'Receiving Out' THEN qty END) AS `receiving_out`
    ,sum(CASE `data` WHEN 'Stock'         THEN qty END) AS `stock`
FROM  `full_report`