如何避免嵌套查询实现客户订单统计且无需展示D365_ID等多余字段

无嵌套直接实现方案 #

你可以将完整的CASE表达式直接放入GROUP BY子句，既不需要嵌套查询，也不需要返回多余的D365_ID、STORE_NAME字段，代码如下：

SELECT 
    CUSTOMER_EMAIL,
    SUM(line_price) AS VALUE_Total,
    COUNT(DISTINCT order_id) AS Orders_Total, 
    CASE 
        WHEN D365_ID = 'NORD' THEN 'EURO'
        WHEN D365_ID = 'US' THEN 'USD'
        WHEN D365_ID = 'UK' THEN 'GBP'
        WHEN D365_ID = 'LMG' THEN 'EURO'
        WHEN D365_ID = 'SWE' THEN 'SEK'     
        WHEN D365_ID = 'Unknown' THEN
            CASE 
                WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
                WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
                WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
                ELSE 'Unknown'
            END
        ELSE 'Unknown'
    END AS CURRENCY
FROM "CUSTOMER_TRANSACTIONS"
GROUP BY 
    CUSTOMER_EMAIL,
    CASE 
        WHEN D365_ID = 'NORD' THEN 'EURO'
        WHEN D365_ID = 'US' THEN 'USD'
        WHEN D365_ID = 'UK' THEN 'GBP'
        WHEN D365_ID = 'LMG' THEN 'EURO'
        WHEN D365_ID = 'SWE' THEN 'SEK'     
        WHEN D365_ID = 'Unknown' THEN
            CASE 
                WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
                WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
                WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
                ELSE 'Unknown'
            END
        ELSE 'Unknown'
    END

可选高可读性实现方案 #

如果觉得CASE表达式重复编写冗余，可以用CTE（公用表表达式）代替嵌套子查询，逻辑更清晰，目前主流MySQL、PostgreSQL、SQL Server、Oracle等数据库的优化器都会对该写法生成和无嵌套写法几乎一致的执行计划，性能差异可忽略：

WITH transaction_with_currency AS (
    SELECT 
        CUSTOMER_EMAIL,
        line_price,
        order_id,
        CASE 
            WHEN D365_ID = 'NORD' THEN 'EURO'
            WHEN D365_ID = 'US' THEN 'USD'
            WHEN D365_ID = 'UK' THEN 'GBP'
            WHEN D365_ID = 'LMG' THEN 'EURO'
            WHEN D365_ID = 'SWE' THEN 'SEK'     
            WHEN D365_ID = 'Unknown' THEN
                CASE 
                    WHEN STORE_NAME LIKE 'DE%' THEN 'EURO'
                    WHEN STORE_NAME LIKE 'SE EUR%' THEN 'EURO'
                    WHEN STORE_NAME LIKE 'SE SEK%' THEN 'SEK'
                    ELSE 'Unknown'
                END
            ELSE 'Unknown'
        END AS CURRENCY
    FROM "CUSTOMER_TRANSACTIONS"
)
SELECT 
    CUSTOMER_EMAIL,
    CURRENCY,
    SUM(line_price) AS VALUE_Total,
    COUNT(DISTINCT order_id) AS Orders_Total
FROM transaction_with_currency
GROUP BY CUSTOMER_EMAIL, CURRENCY

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