📚 课程目录第4章 SQL进阶

4. SQL 进阶：多表连接与子查询

在真实 KPI 看板构建中，数据往往分散在多个业务表中。本章将系统讲解如何通过 INNER JOIN、LEFT JOIN、RIGHT JOIN、UNION 以及 子查询 将分散的数据整合为可供 ECharts 直接消费的宽表或聚合结果。

4.1 多表连接的核心逻辑

连接（JOIN）的本质是基于两个表之间的共同字段（通常是主键与外键）进行行级组合。理解连接类型的关键在于驱动表与匹配表的关系。

💡 连接口诀：INNER 只留匹配行，LEFT 保全左表行，RIGHT 保全右表行，FULL 两边全保留。

4.1.1 连接类型对比表

连接类型	返回行数规则	典型场景	KPI 看板示例
INNER JOIN	仅返回两表匹配成功的行	只分析有完整关联记录的数据	订单表 + 用户表，只取有真实用户的订单
LEFT JOIN	返回左表全部行，右表无匹配时填充 NULL	保留主表全部记录，补充关联信息	日期维度表 LEFT JOIN 销售表，补全无销售日期
RIGHT JOIN	返回右表全部行，左表无匹配时填充 NULL	以右表为主体的补充查询	商品表 RIGHT JOIN 库存表，保留所有库存记录

4.1.2 连接过程可视化

下图展示了 INNER JOIN 与 LEFT JOIN 在数据行层面的差异：

4.2 三种 JOIN 的 SQL 语法与 KPI 案例

4.2.1 INNER JOIN — 只取交集

场景：计算已支付订单的销售额汇总，只统计既有订单记录又有支付成功的行。

SELECT
    o.order_date,
    SUM(p.amount) AS daily_paid_revenue
FROM orders o
INNER JOIN payments p
    ON o.order_id = p.order_id
WHERE p.status = 'success'
GROUP BY o.order_date
ORDER BY o.order_date;

  📊 ECharts 看板应用：上述结果可直接用于折线图 series.data，展示每日已支付销售额趋势。

4.2.2 LEFT JOIN — 保留主表全部

场景：构建日期维度表，确保看板时间轴连续，无销售日期显示 0 值。

SELECT
    d.date_value,
    COALESCE(SUM(s.sales_amount), 0) AS daily_sales
FROM date_dim d
LEFT JOIN sales s
    ON d.date_value = s.sale_date
WHERE d.date_value BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY d.date_value
ORDER BY d.date_value;

⚠️ 避坑提示：LEFT JOIN 后右表字段可能为 NULL，务必使用 COALESCE 或 IFNULL 处理，否则 ECharts 图表会出现断裂或 NaN。

4.2.3 RIGHT JOIN — 以右表为主

场景：分析所有商品库存状态，即使某些商品从未被销售过也要保留。

SELECT
    p.product_name,
    i.warehouse_id,
    i.quantity
FROM sales s
RIGHT JOIN inventory i
    ON s.product_id = i.product_id
GROUP BY p.product_name, i.warehouse_id, i.quantity;

💡 实践建议：RIGHT JOIN 在逻辑上等价于交换表顺序的 LEFT JOIN。为保持可读性，推荐统一使用 LEFT JOIN，将主表放在左侧。

4.3 UNION — 纵向合并结果集

UNION 用于将多个 SELECT 查询的结果纵向堆叠，要求各查询的列数、列顺序、数据类型一致。

4.3.1 UNION vs UNION ALL

操作符	去重行为	性能	适用场景
UNION	自动去除重复行	较慢（需排序去重）	合并后需要唯一记录
UNION ALL	保留所有行（含重复）	较快（直接追加）	合并明细数据，无去重要求

4.3.2 看板实战：合并线上线下销售

SELECT
    'Online' AS channel,
    order_date,
    SUM(amount) AS revenue
FROM online_orders
GROUP BY order_date

UNION ALL

SELECT
    'Offline' AS channel,
    sale_date,
    SUM(amount) AS revenue
FROM offline_sales
GROUP BY sale_date

ORDER BY order_date, channel;

  📊 ECharts 看板应用：结果集可驱动堆叠柱状图，用 channel 字段作为系列分组，展示线上线下渠道每日贡献对比。

4.4 子查询 — 查询中的查询

子查询是嵌套在外部查询内部的 SELECT 语句，常用于动态过滤、聚合比较或派生表。

4.4.1 标量子查询（返回单值）

场景：在 KPI 看板中展示每个产品销售额与整体平均值的对比。

SELECT
    product_name,
    SUM(sales_amount) AS product_total,
    (SELECT AVG(total_sales)
     FROM (SELECT SUM(sales_amount) AS total_sales
           FROM sales
           GROUP BY product_id) AS avg_tbl
    ) AS avg_all_products
FROM sales
GROUP BY product_name;

4.4.2 派生表子查询（FROM 子句）

场景：先计算每日 KPI，再在外层进行周聚合。

SELECT
    week_start,
    AVG(daily_revenue) AS avg_daily_revenue
FROM (
    SELECT
        DATE_TRUNC('week', order_date) AS week_start,
        order_date,
        SUM(amount) AS daily_revenue
    FROM orders
    GROUP BY order_date
) AS daily_kpi
GROUP BY week_start
ORDER BY week_start;

4.4.3 EXISTS 子查询（存在性判断）

场景：筛选出至少有一次高价值交易的客户。

SELECT
    customer_id,
    customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 10000
);

4.5 多表连接与子查询的架构关系

下图展示了从原始业务表到 ECharts 看板数据的完整数据流：

4.6 综合实战：构建 KPI 看板宽表

以下 SQL 综合运用了 LEFT JOIN、子查询与聚合，生成可直接用于 ECharts 的日维度 KPI 宽表：

SELECT
    d.date_value,
    COALESCE(SUM(o.amount), 0) AS daily_revenue,
    COUNT(DISTINCT o.order_id) AS daily_orders,
    COUNT(DISTINCT o.customer_id) AS daily_buyers,
    ROUND(
        COALESCE(SUM(o.amount), 0) / NULLIF(COUNT(DISTINCT o.order_id), 0),
        2
    ) AS avg_order_value
FROM date_dim d
LEFT JOIN orders o
    ON d.date_value = o.order_date
LEFT JOIN (
    SELECT order_id, SUM(amount) AS amount
    FROM payments
    WHERE status = 'success'
    GROUP BY order_id
) p
    ON o.order_id = p.order_id
WHERE d.date_value BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY d.date_value
ORDER BY d.date_value;

  📊 看板字段映射：

  daily_revenue → 折线图 Y 轴（日销售额）

  daily_orders → 柱状图 Y 轴（日订单量）

  avg_order_value → 仪表盘指标（客单价）

  daily_buyers → 面积图 Y 轴（日购买人数）

4.7 本章小结

INNER JOIN 用于只保留匹配行的精确分析；LEFT JOIN 用于保留主表全部行的补全场景；RIGHT JOIN 可被 LEFT JOIN 替代。
UNION ALL 性能优于 UNION，无去重要求时优先使用。
子查询 分为标量子查询、派生表子查询、EXISTS 子查询，分别适用于单值返回、中间表构建、存在性判断。
多表连接与子查询的组合使用，是构建 KPI 看板宽表的核心技能，直接决定 ECharts 图表的数据质量与刷新效率。