如何用单条MySQL语句按城市及里程区间生成发票统计结果？

需求说明 #

数据库表perorder包含KM（里程）、city（城市）、total_amount（总金额）字段，需按以下规则生成发票统计：

• 按城市（DXB迪拜、AUH阿布扎比）分组
• 按里程区间（1-3Km、4-5Km、6-7Km）划分统计维度
• 每个分组需统计：条目数、总金额、对应里程区间的费率、费率×条目数的金额、5%税费、含税费的总金额
• 费率规则：DXB和AUH使用相同里程区间，但费率不同：
  • DXB：1-3Km费率9，4-5Km费率11，6-7Km费率13
  • AUH：需替换为实际对应里程区间的费率

单条SQL解决方案 #

SELECT
    city,
    CASE
        WHEN KM BETWEEN 1 AND 3 THEN '1-3 Kms'
        WHEN KM BETWEEN 4 AND 5 THEN '4-5 Kms'
        WHEN KM BETWEEN 6 AND 7 THEN '6-7 Kms'
        ELSE '其他里程'
    END AS mileage_range,
    COUNT(KM) AS order_count,
    SUM(total_amount) AS total_amount_sum,
    -- 获取对应城市和里程区间的费率
    CASE
        WHEN city LIKE '%XB' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 9
                WHEN KM BETWEEN 4 AND 5 THEN 11
                WHEN KM BETWEEN 6 AND 7 THEN 13
                ELSE 0
            END
        WHEN city LIKE '%UH' THEN -- 匹配AUH阿布扎比
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 10 -- 替换为AUH实际费率
                WHEN KM BETWEEN 4 AND 5 THEN 12 -- 替换为AUH实际费率
                WHEN KM BETWEEN 6 AND 7 THEN 14 -- 替换为AUH实际费率
                ELSE 0
            END
        ELSE 0
    END AS rate,
    -- 计算费率×订单数的金额
    (CASE
        WHEN city LIKE '%XB' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 9
                WHEN KM BETWEEN 4 AND 5 THEN 11
                WHEN KM BETWEEN 6 AND 7 THEN 13
                ELSE 0
            END
        WHEN city LIKE '%UH' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 10
                WHEN KM BETWEEN 4 AND 5 THEN 12
                WHEN KM BETWEEN 6 AND 7 THEN 14
                ELSE 0
            END
        ELSE 0
    END) * COUNT(KM) AS rate_total,
    -- 计算5%税费（保留两位小数）
    ROUND((CASE
        WHEN city LIKE '%XB' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 9
                WHEN KM BETWEEN 4 AND 5 THEN 11
                WHEN KM BETWEEN 6 AND 7 THEN 13
                ELSE 0
            END
        WHEN city LIKE '%UH' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 10
                WHEN KM BETWEEN 4 AND 5 THEN 12
                WHEN KM BETWEEN 6 AND 7 THEN 14
                ELSE 0
            END
        ELSE 0
    END) * COUNT(KM) * 0.05, 2) AS tax,
    -- 计算含税费的总金额
    (CASE
        WHEN city LIKE '%XB' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 9
                WHEN KM BETWEEN 4 AND 5 THEN 11
                WHEN KM BETWEEN 6 AND 7 THEN 13
                ELSE 0
            END
        WHEN city LIKE '%UH' THEN
            CASE
                WHEN KM BETWEEN 1 AND 3 THEN 10
                WHEN KM BETWEEN 4 AND 5 THEN 12
                WHEN KM BETWEEN 6 AND 7 THEN 14
                ELSE 0
            END
        ELSE 0
    END) * COUNT(KM) * 1.05 AS total_with_tax
FROM perorder
WHERE
    (city LIKE '%XB' OR city LIKE '%UH')
    AND KM BETWEEN 1 AND 7
GROUP BY city, mileage_range
ORDER BY city, mileage_range;

代码说明 #

1. 里程区间映射：通过CASE WHEN将数值型的KM转换为可读性更强的区间文本，作为分组维度之一。
2. 费率匹配：嵌套CASE先判断城市，再根据里程区间返回对应费率，AUH部分的费率需替换为实际业务数值。
3. 统计计算：
   • order_count：统计每个分组的订单条目数
   • total_amount_sum：统计该分组的原始总金额
   • rate_total：该分组的费率×订单数的金额
   • tax：按5%计算的税费，用ROUND函数保留两位小数
   • total_with_tax：包含税费的最终总金额
4. 过滤与排序：WHERE子句限定目标城市和有效里程范围，GROUP BY确保按城市+里程区间分组统计，ORDER BY让结果按城市和里程区间有序展示。

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