一个复杂的Case函数选择列的IFNULL

Question

如何修正我的IFNULL，使它不会破坏下面的内容？如果我删除了IFNULL，它就会正常工作，但是我需要从该列中计算数据，并且需要去掉空值并替换为0。

 IFNULL(CASE 
    WHEN A.Industry = 'Transportation & Warehousing' THEN 26
    WHEN A.Industry = 'Construction' THEN 19
    WHEN A.Industry = 'Field Services' THEN 26
    WHEN A.Industry = 'Wholesale Trade' THEN 26
    WHEN A.Industry = 'Manufacturing' THEN 30
    WHEN A.Industry = 'Consumer Products' THEN 26
    WHEN A.Industry = 'Retail Trade' THEN 26
    WHEN A.Industry = 'Passenger Transit' THEN 25
    WHEN A.Industry = 'Mining, Quarrying, Oil & Gas' THEN 25
    WHEN A.Industry = 'Food & Beverage' THEN 26
    WHEN A.Industry = 'Utilities' THEN 26
    WHEN A.Industry = 'Health Care & Social Assistance' THEN 25
    WHEN A.Industry = 'Government' THEN 26
    WHEN A.Industry = 'Educational Services' THEN 25
    ELSE 254
    END * Number_Of_Vehicles *0.99) + 
(CASE 
    WHEN A.Industry = 'Transportation & Warehousing' THEN 34
    WHEN A.Industry = 'Construction' THEN 32
    WHEN A.Industry = 'Field Services' THEN 33
    WHEN A.Industry = 'Wholesale Trade' THEN 36
    WHEN A.Industry = 'Manufacturing' THEN 39
    WHEN A.Industry = 'Consumer Products' THEN 42
    WHEN A.Industry = 'Retail Trade' THEN 31
    WHEN A.Industry = 'Passenger Transit' THEN 32
    WHEN A.Industry = 'Mining, Quarrying, Oil & Gas' THEN 32
    WHEN A.Industry = 'Food & Beverage' THEN 23
    WHEN A.Industry = 'Utilities' THEN 32
    WHEN A.Industry = 'Health Care & Social Assistance' THEN 32
    WHEN A.Industry = 'Government' THEN 42
    WHEN A.Industry = 'Educational Services' THEN 39
    ELSE 32
    END * Number_Of_Vehicles * 0.49),0)

尝试添加ifnull，但不确定在这种情况下如何使用正确的格式进行操作。

Answer 1

直接的问题是括号，这是不平衡的。您不需要在CASE的周围加上括号，IFNULL()函数只需要一对。

此外，您还可以简化CASE表达式，使选中的列不再重复一次又一次。我还发现，如果有一个具有CASE分支管理整个算术的WHEN表达式，而不是两个具有类似分支的不同表达式，则更容易理解。

我们可以这样说：

IFNULL(
    CASE A.Industry 
        WHEN 'Transportation & Warehousing'     THEN 26 * Number_Of_Vehicles * 0.99 + 34 * Number_Of_Vehicles * 0.49
        WHEN 'Construction'                     THEN 19 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Field Services'                   THEN 26 * Number_Of_Vehicles * 0.99 + 33 * Number_Of_Vehicles * 0.49
        WHEN 'Wholesale Trade'                  THEN 26 * Number_Of_Vehicles * 0.99 + 36 * Number_Of_Vehicles * 0.49
        WHEN 'Manufacturing'                    THEN 30 * Number_Of_Vehicles * 0.99 + 39 * Number_Of_Vehicles * 0.49
        WHEN 'Consumer Products'                THEN 26 * Number_Of_Vehicles * 0.99 + 42 * Number_Of_Vehicles * 0.49
        WHEN 'Retail Trade'                     THEN 26 * Number_Of_Vehicles * 0.99 + 31 * Number_Of_Vehicles * 0.49
        WHEN 'Passenger Transit'                THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Mining, Quarrying, Oil & Gas'     THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Food & Beverage'                  THEN 26 * Number_Of_Vehicles * 0.99 + 23 * Number_Of_Vehicles * 0.49
        WHEN 'Utilities'                        THEN 26 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Health Care & Social Assistance'  THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Government'                       THEN 26 * Number_Of_Vehicles * 0.99 + 42 * Number_Of_Vehicles * 0.49
        WHEN 'Educational Services'             THEN 25 * Number_Of_Vehicles * 0.99 + 39 * Number_Of_Vehicles * 0.49
        ELSE                                        254 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
    END,
    0
)