GROUP BY子句使用CASE语句时如何为对应分组列设置别名

解决方法 #

你只需要把GROUP BY子句中用到的CASE表达式同步放到SELECT查询列表中，再通过AS关键字设置自定义列名即可，修改后的完整查询语句如下：

SELECT 
    CASE 
        WHEN y.OFFER_ID IS NULL 
            THEN 'Null' 
        WHEN y.OFFER_ID IN ('1882', '1883', '4554', '4576') 
            THEN 'in-store' 
        ELSE 'web'  
    END AS order_channel, -- 这里的order_channel就是自定义的分组列名，可按需修改
    SUM(item_subtotal) AS rev
FROM 
    [ADAS].[dbo].[ADAS_ORDERS] x
LEFT JOIN 
    ADAS.dbo.ADAS_SUBSCRIPTIONS y ON x.SUBSCRIPTION_KEY = y.SUBSCRIPTION_KEY
                                  AND x.CUSTOMER_ID = y.customer_id
                                  AND x.ITEM_NUM = y.ITEM_NUM
WHERE
    x.ORDER_DATE BETWEEN '10-01-2021' AND '10-31-2021'
    AND x.ORDER_STATUS = 'success' 
    AND x.IMPULSE_FLG = 'n'
GROUP BY
    CASE 
        WHEN y.OFFER_ID IS NULL 
            THEN 'Null' 
        WHEN y.OFFER_ID IN ('1882', '1883', '4554', '4576') 
            THEN 'in-store' 
        ELSE 'web'  
    END
ORDER BY 
    1

注意：SELECT中的CASE表达式必须和GROUP BY中的完全一致，否则会触发语法报错。

内容的提问来源于stack exchange，提问作者Ross