要写好精准的复杂 SQL 查询，我们应该先把三个问题弄明白：一是这查询要对应啥实际业务场景，比如是电商算销量还是企业查库存；二是表结构（含：字段名、数据类型、关联关系），通俗地说，我们要清楚表的情况，包括表里有哪些字段、每个字段是数字还是文字，以及表和表之间怎么关联；三是明确查询目标，比如要多表统计数据、按条件筛选信息，还是给数据排名。下面我们就用一个常见的实际业务场景示例来演绎“电商订单多维度统计”。在这个示例里，我们会用到多表关联、聚合计算、窗口函数、条件过滤等等这些复杂逻辑。借着这个示例，我们一步步来学习怎样写出精准的复杂 SQL 查询？

示例场景：统计2024年Q1（1-3月）各省份的订单数据，需包含：省份、订单总数、支付订单数、退款订单数、总销售额、客单价、TOP3热销商品名称及销量。

一、创建模拟数据表及插入模拟数据

1、orders（订单表）：order_id(订单ID)、user_id(用户ID)、province(省份)、order_time(下单时间)、pay_status(支付状态：1=已支付，0=未支付)、refund_status(退款状态：1=已退款，0=未退款)

2、order_details（订单明细表）：detail_id(明细ID)、order_id(订单ID)、product_id(商品ID)、quantity(购买数量)、unit_price(单价)

3、products（商品表）：product_id(商品ID)、product_name(商品名称)

二、复杂SQL查询语句

WITH 
-- 子查询1：计算各订单的总金额（关联订单表和明细表）
order_amount AS (
    SELECT 
        o.order_id, -- 订单ID
        o.province, -- 省份
        o.pay_status, -- 支付状态
        o.refund_status, -- 退款状态
        SUM(od.quantity * od.unit_price) AS order_total -- 计算单订单总金额，通过订单明细表中的数量和单价相乘后求和
    FROM orders o
    -- 左连接订单明细表，通过订单ID进行关联
    LEFT JOIN order_details od ON o.order_id = od.order_id
    -- 筛选出2024年第一季度（1-3月）的订单
    WHERE o.order_time BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59'
    -- 按照订单ID、省份、支付状态、退款状态进行分组
    GROUP BY o.order_id, o.province, o.pay_status, o.refund_status
),

-- 子查询2：计算各省份的基础订单统计（聚合计算）
province_order_stats AS (
    SELECT 
        province, -- 省份
        COUNT(order_id) AS total_orders, -- 统计每个省份的订单总数
        -- 统计支付订单数，通过判断支付状态为1时计数
        SUM(CASE WHEN pay_status = 1 THEN 1 ELSE 0 END) AS paid_orders, 
        -- 统计退款订单数，通过判断退款状态为1时计数
        SUM(CASE WHEN refund_status = 1 THEN 1 ELSE 0 END) AS refunded_orders, 
        -- 计算总销售额，只统计支付状态为1的订单总金额（仅统计已支付，包含已支付已退款）
        SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END) AS total_sales, 
        -- 计算客单价，总销售额除以支付订单数，使用NULLIF函数避免除以0的错误，并用ROUND函数保留2位小数
        ROUND(
            SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END) 
            / NULLIF(SUM(CASE WHEN pay_status = 1 THEN 1 ELSE 0 END), 0), 
            2
        ) AS avg_transaction  -- 客单价
    FROM order_amount
    -- 按照省份进行分组
    GROUP BY province
),

-- 子查询3：计算各省份的商品销量排名（窗口函数）
province_product_sales AS (
    SELECT 
        o.province, -- 省份
        p.product_name, -- 商品名称
        SUM(od.quantity) AS product_sales, -- 统计每个省份中各商品的销量，通过订单明细表中的数量求和
        -- 使用窗口函数ROW_NUMBER()，按照省份分区，根据商品销量降序排名
        ROW_NUMBER() OVER (PARTITION BY o.province ORDER BY SUM(od.quantity) DESC) AS sales_rank
    FROM orders o
    -- 左连接订单明细表，通过订单ID关联
    LEFT JOIN order_details od ON o.order_id = od.order_id
    -- 左连接商品表，通过商品ID关联
    LEFT JOIN products p ON od.product_id = p.product_id
    -- 筛选出2024年第一季度（1-3月）且支付状态为1的订单
    WHERE o.order_time BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59'
      AND o.pay_status = 1
    -- 按照省份和商品名称进行分组
    GROUP BY o.province, p.product_name
),

-- 子查询4：筛选各省份TOP3热销商品（行转列，便于最后展示）
province_top3_products AS (
    SELECT 
        province, -- 省份
        -- 用CASE WHEN将排名1-3的商品名称和销量拼接
        CONCAT(
            '1.', MAX(CASE WHEN sales_rank = 1 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 1 THEN product_sales END), '件); ',
            '2.', MAX(CASE WHEN sales_rank = 2 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 2 THEN product_sales END), '件); ',
            '3.', MAX(CASE WHEN sales_rank = 3 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 3 THEN product_sales END), '件)'
        ) AS top3_products
    FROM province_product_sales
    -- 仅保留排名前3的商品
    WHERE sales_rank <= 3
    -- 按照省份进行分组
    GROUP BY province
)

-- 关联所有子查询，输出完整统计结果
SELECT 
    pos.province, -- 省份
    pos.total_orders, -- 订单总数
    pos.paid_orders, -- 支付订单数
    pos.refunded_orders, -- 退款订单数
    pos.total_sales, -- 总销售额
    pos.avg_transaction, -- 客单价
    ptp.top3_products -- TOP3热销商品名称及销量
FROM province_order_stats pos
-- 左连接子查询4，通过省份进行关联
LEFT JOIN province_top3_products ptp ON pos.province = ptp.province
-- 过滤掉无支付订单的省份（可选）
WHERE pos.paid_orders > 0
-- 按照总销售额降序排序
ORDER BY pos.total_sales DESC;

三、复杂逻辑说明

1、多表关联：通过orders.order_id关联order_details，通过order_details.product_id关联products，让我们来实现“订单-明细-商品”的全链路数据获取。
2、CTE子查询（WITH子句）：我们将复杂逻辑拆分为4个独立子查询，降低代码耦合度，便于后期维护。
3、条件聚合：我们用CASE WHEN + SUM计算支付/退款订单数，仅统计符合条件的数据。
4、窗口函数：ROW_NUMBER()按省份分区对商品销量排名，实现“各省份TOP3商品”的筛选。
5、特殊处理：用NULLIF避免客单价计算时“除以0”的错误，用ROUND保留2位小数，用CONCAT拼接TOP3商品信息。

四、查询结果（2024年Q1统计）

已按“总销售额”降序排序，过滤无支付订单的省份：

结果逻辑验证（以广东省为例）：
1、总订单数=3：订单1001、1002、1004（含未支付的1004）；
2、支付订单数=2：仅1001（已支付未退款）、1002（已支付已退款）；
3、总销售额=896：1001金额=2 * 199+1 * 299=697，1002金额=1 * 199=199，合计697+199=896；
4、TOP3商品：广东省销量最高的是“无线蓝牙耳机”（1001买2件+1002买1件=3件）；其次是“智能手表”（1001买1件）；无第三名，故显示0件。

五、业务语义优化：无第三名商品时，可将 “0 件” 改为 “无”，更符合业务语义

原查询在拼接 TOP3 商品时，对于排名不足3位的商品，会显示 0件，这在业务语义上不够友好。将空缺的排名显示为“无”，在部分人看来，似乎更符合业务语义，更能准确地反映“某个排名没有商品”的业务现状，而不是误导性地显示销量为0。例如：广东省 TOP3 商品可优化为：
1.无线蓝牙耳机(3件); 2.智能手表(1件); 3.无

实现方式（修改子查询 4 的 CONCAT 逻辑）：在province_top3_products的CONCAT函数中，对第三名增加IFNULL判断：

CONCAT(
    '1.', MAX(CASE WHEN sales_rank = 1 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 1 THEN product_sales END), '件); ',
    '2.', MAX(CASE WHEN sales_rank = 2 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 2 THEN product_sales END), '件); ',
    '3.', IFNULL(MAX(CASE WHEN sales_rank = 3 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无')
) AS top3_products

以下是修改后的 province_top3_products 子查询：

-- 子查询4：筛选各省份TOP3热销商品（行转列，便于最后展示）
province_top3_products AS (
    SELECT
        province,
        -- 优化后的CONCAT逻辑：使用COALESCE处理空值，将NULL转换为'无'
        CONCAT(
            '1.', COALESCE(MAX(CASE WHEN sales_rank = 1 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无'), '; ',
            '2.', COALESCE(MAX(CASE WHEN sales_rank = 2 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无'), '; ',
            '3.', COALESCE(MAX(CASE WHEN sales_rank = 3 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无')
        ) AS top3_products
    FROM province_product_sales
    WHERE sales_rank <= 3
    GROUP BY province
)

修改后的查询结果：

修改点说明：

1、使用 COALESCE (或 IFNULL) 函数：COALESCE(MAX(...), '无') 确保了当没有对应排名的商品时，会用“无”来填充，而不是显示一个 NULL 或拼接出 (0件) 的结果。 2、调整拼接逻辑：我们将商品名称和销量的拼接 CONCAT(product_name, '(', product_sales, '件)') 移到了 CASE WHEN 内部。这样，对于某一排名，要么得到完整的字符串（如：“便携充电宝(3件)”），要么得到 NULL，然后由 COALESCE 将其转换为“无”。

六、如何处理“已支付但已退款”的订单？

在不同的业务场景下，对“销售额”的定义不同，这直接决定了我们是否应该将“已退款的支付订单”计入总销售额。现在有两种主流的统计口径：

1、口径一：GMV (Gross Merchandise Volume) / 成交总额

• 定义：只要订单支付成功，无论后续是否退款，都计入销售额。
• 优点：反映了我们前端的交易规模和运营能力。
• 缺点：与我们的实际收入不符。
• 原查询使用的就是此口径：
• 逻辑：SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END)
• 它只判断“是否支付”，不判断“是否退款”。因此，广东省订单1002（支付了199元，虽然后续退款）依然被计入了总销售额（896元中包含了这199元）。

2、口径二：净销售额 (Net Sales) / 实际收入

• 定义：只计入最终未退款的支付订单金额。已支付的款项如果后续被退回，则不应计入。
• 优点：更准确地反映了公司的实际收入，是财务部门更关注的指标。
• 缺点：无法体现前端的交易(流)量。

3、如何修改SQL以符合“净销售额”口径？

如果我们需要采用口径二（净销售额），只需修改 province_order_stats 子查询中的聚合条件即可。

将：

SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END) AS total_sales

修改为：

-- 计算总销售额（total_sales）
-- 逻辑说明：
-- 1.使用CASE语句筛选符合条件的订单
-- 2.当订单满足"已支付（pay_status = 1）且未退款（refund_status = 0）"时，计入该订单的总金额（order_total）
-- 3.不满足上述条件的订单，按0处理（不贡献销售额）
-- 4.最后通过SUM函数对所有符合条件的订单金额求和，得到总销售额
SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN order_total ELSE 0 END) AS total_sales

这个修改意味着：只统计那些“已支付”且“未退款”的订单金额。

修改后的结果验证（以广东省为例）：

• 原GMV口径（有退款也计入）：
• 1001订单：697元
• 1002订单：199元 (虽退款，仍计入)
• 总销售额 = 697 + 199 = 896元
• 净销售额口径（退款不计入）：
• 1001订单：697元 (支付且未退款)
• 1002订单：0元 (支付但已退款，故不计入)
• 总销售额 = 697 + 0 = 697元

说明：客单价也需要相应调整，分母应是“支付且未退款的订单数”，否则逻辑会矛盾（销售额是697元，但分母paid_orders仍是2，会得到348.5的错误客单价）。更严谨的写法是：

-- 计算平均交易金额，保留两位小数，别名为avg_transaction
ROUND(
    -- 分子：计算有效订单的总金额
    -- 当支付状态为1（已支付）且退款状态为0（未退款）时，取订单总金额，否则取0，然后求和
    SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN order_total ELSE 0 END) 
    -- 分母：计算有效订单的数量（避免除以0的情况）
    -- 当支付状态为1且退款状态为0时，计数1，否则计数0，然后求和
    -- NULLIF函数用于当分母为0时返回NULL，避免出现除0错误
    / NULLIF(SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN 1 ELSE 0 END), 0), 
    -- 保留两位小数
    2
) AS avg_transaction

在严谨的财务或业务分析中，是否将退款订单计入销售额，会极大影响分析结论。选择哪种口径取决于我们的统计目的。如果是为了评估我们销售团队的业绩或我们的交易规模，常用 GMV口径。如果是为了分析我们的实际营收或利润，必须使用 净销售额口径。因此，在编写任何数据查询前，我们一定要与需求方（如：业务部门、财务部门等）明确指标的具体统计口径。

在复杂 SQL 查询的领域里，从明晰业务场景到构建精准查询，再到优化语义、适配不同统计口径，每一步都暗藏玄机。通过“电商订单多维度统计”这一实际业务场景示例，我们领略了多表关联、聚合计算等等复杂又精准的 SQL 查询技巧。在未来的实践中，我们要以需求为导向，精准雕琢每一个复杂 SQL 查询，挖掘数据深处的价值。