SQL 分组查询如何实现跨表多列统计？-SQL-PHP中文网

跨表多列统计需通过JOIN关联表后用GROUP BY和聚合函数实现，核心是正确处理多对多关系避免数据膨胀，常用COUNT(DISTINCT)或先聚合再JOIN；为提升性能应建立索引、尽早过滤数据、选择合适JOIN类型并避免SELECT *；灵活统计可借助CASE表达式实现条件聚合，利用ROLLUP、CUBE、GROUPING SETS生成多维汇总，结合窗口函数进行组内分析。

sql 分组查询如何实现跨表多列统计？

SQL 分组查询实现跨表多列统计，核心在于利用

JOIN

登录后复制

操作将所需数据从不同表关联起来，形成一个逻辑上的“大表”，然后在这个“大表”上应用

GROUP BY

登录后复制

子句以及各种聚合函数（如

COUNT

登录后复制

SUM

登录后复制

AVG

登录后复制

MAX

登录后复制

MIN

登录后复制

等）来完成多列的统计计算。这就像是把散落在各处的数据碎片，先用胶水（

JOIN

登录后复制

）粘合在一起，再用一个漏斗（

GROUP BY

登录后复制

）按你想要的维度进行汇总，同时在汇总过程中对特定列进行计数、求和等操作。

解决方案

要实现跨表多列统计，我们的基本思路是：

确定统计目标和维度： 你想统计什么？按什么维度统计？比如，我们想统计每个客户的订单总数、订单总金额以及他们购买的商品种类数。
识别相关表： 哪些表包含了我们需要的数据？例如，
```
customers
```
登录后复制
表（客户信息）、
```
orders
```
登录后复制
表（订单信息）、
```
order_items
```
登录后复制
表（订单明细，连接订单与商品）。
建立表间关联（JOIN）： 使用
```
JOIN
```
登录后复制
语句将这些表根据它们之间的关系连接起来。通常是
```
INNER JOIN
```
登录后复制
，但根据需求也可能是
```
LEFT JOIN
```
登录后复制
等。
选择聚合列和分组列： 确定哪些列需要进行聚合计算（如
```
SUM(amount)
```
登录后复制
,
```
COUNT(order_id)
```
登录后复制
,
```
COUNT(DISTINCT product_id)
```
登录后复制
），以及哪些列作为分组的依据（如
```
customer_id
```
登录后复制
,
```
customer_name
```
登录后复制
）。
编写 SQL 查询： 将上述步骤组合成一个完整的 SQL 查询。

示例：

假设我们有以下简化表结构：

```
customers
```
登录后复制
表:
```
customer_id
```
登录后复制
(PK),
```
customer_name
```
登录后复制
```
orders
```
登录后复制
表:
```
order_id
```
登录后复制
(PK),
```
customer_id
```
登录后复制
(FK),
```
order_date
```
登录后复制
,
```
total_amount
```
登录后复制
```
order_items
```
登录后复制
表:
```
item_id
```
登录后复制
(PK),
```
order_id
```
登录后复制
(FK),
```
product_id
```
登录后复制
(FK),
```
quantity
```
登录后复制
,
```
price
```
登录后复制
```
products
```
登录后复制
表:
```
product_id
```
登录后复制
(PK),
```
product_name
```
登录后复制

现在，我们想统计每个客户的：

总订单数
订单总金额
购买的商品种类数（去重）

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders, -- 统计订单总数
    SUM(o.total_amount) AS total_spent,         -- 统计订单总金额
    COUNT(DISTINCT oi.product_id) AS distinct_products_purchased -- 统计购买的商品种类数
FROM
    customers c
INNER JOIN
    orders o ON c.customer_id = o.customer_id
INNER JOIN
    order_items oi ON o.order_id = oi.order_id
GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY
    total_spent DESC;

登录后复制

这个查询通过两次

INNER JOIN

登录后复制

将

customers

登录后复制

orders

登录后复制

order_items

登录后复制

三张表关联起来。然后，我们根据

customer_id

登录后复制

和

customer_name

登录后复制

进行分组。在聚合函数中，

COUNT(DISTINCT o.order_id)

登录后复制

确保每个订单只计算一次，

SUM(o.total_amount)

登录后复制

累加每个客户的订单总金额，而

COUNT(DISTINCT oi.product_id)

登录后复制

则统计每个客户购买的去重商品种类。

跨表统计中，如何有效处理多对多关系的数据聚合？

在跨表统计中，多对多关系是个常见的“坑”，一不小心就可能导致数据膨胀，进而让聚合结果失真。比如说，一个订单可以包含多个商品，一个商品也可以出现在多个订单中，这就是订单和商品之间的多对多关系，通常会通过一个中间表（如

order_items

登录后复制

）来连接。

当你直接将

customers

登录后复制

、

orders

登录后复制

和

order_items

登录后复制

（甚至是

products

登录后复制

）一股脑儿

JOIN

登录后复制

起来，然后去统计客户的订单数时，问题就来了。如果一个订单里有10个商品，那么在

customers JOIN orders JOIN order_items

登录后复制

后的结果集中，这个订单的信息就会重复出现10次。这时，如果你简单地

COUNT(o.order_id)

登录后复制

，你会得到一个错误的结果，因为同一个订单被重复计数了。

解决这种数据膨胀导致聚合不准的问题，我通常有几个策略：

使用
```
COUNT(DISTINCT column)
```
登录后复制
：这是最直接也最常用的方法。比如上面的例子，
```
COUNT(DISTINCT o.order_id)
```
登录后复制
就能确保即使订单信息因为
```
order_items
```
登录后复制
表的
```
JOIN
```
登录后复制
而重复，最终统计的订单数依然是准确的。同理，
```
COUNT(DISTINCT oi.product_id)
```
登录后复制
也能准确统计去重后的商品种类。这种方法简洁明了，适用于大部分场景。
先聚合再
```
JOIN
```
登录后复制
（子查询或 CTE）： 对于更复杂的场景，或者当你需要在一个多对多关系的“一侧”进行聚合，然后将聚合结果与另一侧关联时，这种方法就非常有效。例如，我们想统计每个客户购买的商品总数量（而非种类数）。如果直接
```
SUM(oi.quantity)
```
登录后复制
，那么如果一个客户的订单有多个商品，订单信息会重复，导致
```
oi.quantity
```
登录后复制
被重复求和。正确的做法可以是：先在
```
order_items
```
登录后复制
表中按
```
order_id
```
登录后复制
和
```
product_id
```
登录后复制
聚合出每个订单中每个商品的实际购买数量，或者直接在
```
order_items
```
登录后复制
表中按
```
order_id
```
登录后复制
聚合出每个订单的商品总数量，然后将这个聚合结果
```
JOIN
```
登录后复制
回
```
orders
```
登录后复制
表和
```
customers
```
登录后复制
表。
```
-- 示例：计算每个客户的商品总购买数量
WITH CustomerProductQuantities AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity) AS total_item_quantity
    FROM
        orders o
    INNER JOIN
        order_items oi ON o.order_id = oi.order_id
    GROUP BY
        o.customer_id
)
SELECT
    c.customer_id,
    c.customer_name,
    cpq.total_item_quantity
FROM
    customers c
INNER JOIN
    CustomerProductQuantities cpq ON c.customer_id = cpq.customer_id
ORDER BY
    cpq.total_item_quantity DESC;
```
登录后复制
通过
```
CustomerProductQuantities
```
登录后复制
这个 CTE，我们首先在
```
orders
```
登录后复制
和
```
order_items
```
登录后复制
的连接结果上，按
```
customer_id
```
登录后复制
聚合了商品总数量，这样就避免了
```
order_items
```
登录后复制
带来的行膨胀问题。然后再将这个预聚合的结果与
```
customers
```
登录后复制
表连接。

这两种方法各有侧重，

COUNT(DISTINCT)

登录后复制

简单直接，适用于计数场景；而先聚合再

JOIN

登录后复制

则更灵活，能处理更复杂的求和、平均等聚合需求，尤其是在中间表可能导致多重膨胀时，它能更好地控制数据量。选择哪种，得看你的具体需求和对性能的考量。

在复杂统计需求下，如何避免 SQL 查询性能瓶颈？

复杂统计查询，尤其涉及到跨表

JOIN

登录后复制

和大量数据聚合时，性能问题是绕不开的。我做数据分析和开发这么久，遇到过太多因为查询写得不够“聪明”而把数据库拖垮的例子。避免性能瓶颈，这事儿真得从多个角度去考虑。

索引是基石，但不是万能药：
- JOIN
  登录后复制
  字段必须有索引：这是最基本的。
```
ON
```
  登录后复制
  子句中的连接字段，无论是主键还是外键，都应该建立索引。没有索引，数据库就得进行全表扫描来匹配数据，那速度可想而知。
- WHERE
  登录后复制
  和
  GROUP BY
  登录后复制
  字段也受益：筛选条件 (
```
WHERE
```
  登录后复制
  ) 和分组字段 (
```
GROUP BY
```
  登录后复制
  ) 上的索引也能显著提高查询效率，因为它能帮助数据库快速定位和组织数据。
- 注意索引的类型和数量： 过多的索引会增加写入（
```
INSERT
```
  登录后复制
  ,
```
UPDATE
```
  登录后复制
  ,
```
DELETE
```
  登录后复制
  ）的开销，而且有些索引类型（比如全文索引）不适用于所有场景。要根据查询模式来选择合适的索引。
尽早过滤数据：

蓝心千询
蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情
- WHERE
  登录后复制
  子句前置：在
```
JOIN
```
  登录后复制
  操作之前，如果能通过
```
WHERE
```
  登录后复制
  子句大幅减少参与
```
JOIN
```
  登录后复制
  的行数，那性能提升会非常明显。数据量越小，
```
JOIN
```
  登录后复制
  和聚合的开销就越小。
- 子查询或 CTE 预过滤： 有时候，你可能需要先从一个表中筛选出少量数据，再用这些数据去
```
JOIN
```
  登录后复制
  大表。这时，使用子查询或 CTE 先完成小范围的筛选和聚合，再进行后续操作，能有效减少中间结果集的大小。
选择合适的
```
JOIN
```
登录后复制
类型：
- INNER JOIN
  登录后复制
  vs.
  LEFT JOIN
  登录后复制
  ：
```
INNER JOIN
```
  登录后复制
  只返回匹配的行，结果集通常最小。
```
LEFT JOIN
```
  登录后复制
  会保留左表的所有行，即使右表没有匹配项，这可能导致结果集更大，处理时间更长。根据你的统计需求，选择最能精确匹配数据的
```
JOIN
```
  登录后复制
  类型。
*避免 `SELECT `：**
- 只选择你真正需要的列。
```
SELECT *
```
  登录后复制
  会导致数据库读取和传输不必要的列数据，尤其当表有大量列或者大文本/二进制列时，性能影响会很显著。
优化聚合函数的使用：
- *`COUNT()
```
vs.
```
  登录后复制
  COUNT(column)
```
vs.
```
  登录后复制
  COUNT(DISTINCT column)
```
：**
```
  登录后复制
  COUNT(*)
```
通常效率最高，因为它只是统计行数。
```
  登录后复制
  COUNT(column)
```
会忽略
```
  登录后复制
  NULL
```
值。
```
  登录后复制
  COUNT(DISTINCT column)` 因为需要去重，通常是效率最低的，因为它需要额外的内存和计算来维护唯一值的集合。在需要去重时，这是必要的，但如果不需要，就避免使用。
- HAVING
  登录后复制
  子句的考量：
```
HAVING
```
  登录后复制
  是在
```
GROUP BY
```
  登录后复制
  之后对聚合结果进行过滤，而
```
WHERE
```
  登录后复制
  是在
```
GROUP BY
```
  登录后复制
  之前对原始数据进行过滤。尽可能使用
```
WHERE
```
  登录后复制
  来减少参与聚合的数据量。
考虑物化视图或汇总表：
- 如果某些复杂的统计报表是频繁查询的，并且数据更新频率不高，那么可以考虑创建物化视图（Materialized View）或者定期生成汇总表（Summary Table）。这些预计算的结果可以极大地加速查询，因为它直接读取已经计算好的数据，而不是每次都重新执行复杂的
```
JOIN
```
  登录后复制
  和聚合。
数据库配置与硬件：
- 最后，别忘了数据库本身的配置和服务器硬件。足够的内存、高性能的 CPU 和快速的存储（SSD）是保证复杂查询性能的物理基础。数据库的参数调优，比如缓存大小、并发连接数等，也对性能有重要影响。这部分往往需要专业的 DBA 来处理。

总的来说，优化复杂 SQL 查询是一个迭代的过程，需要结合具体的业务场景、数据量和数据库特性，通过分析执行计划来找到真正的瓶颈并加以解决。

如何利用 SQL 高级特性实现更灵活的统计维度？

当我们谈到“灵活的统计维度”，往往意味着我们不只满足于单一维度的分组聚合，而是希望在一次查询中就能看到不同粒度、不同组合的聚合结果，或者进行更复杂的条件性聚合。SQL 提供了一些高级特性，能让这些需求变得优雅且高效。

```
CASE
```
登录后复制
表达式与聚合函数的组合： 这是我个人最喜欢用的一个技巧，它能让你在聚合函数内部实现条件逻辑，从而实现“条件性计数”或“条件性求和”。

场景： 统计每个客户的订单总数、已完成订单数和未完成订单数。假设
```
orders
```
登录后复制
表有一个
```
status
```
登录后复制
字段（
```
'completed'
```
登录后复制
,
```
'pending'
```
登录后复制
,
```
'cancelled'
```
登录后复制
）。
```
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    COUNT(CASE WHEN o.status = 'completed' THEN o.order_id END) AS completed_orders,
    COUNT(CASE WHEN o.status = 'pending' THEN o.order_id END) AS pending_orders
FROM
    customers c
INNER JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;
```
登录后复制
这里，
```
COUNT(CASE WHEN ... THEN ... END)
```
登录后复制
的巧妙之处在于，当
```
CASE
```
登录后复制
条件不满足时，它会返回
```
NULL
```
登录后复制
，而
```
COUNT()
```
登录后复制
函数是会忽略
```
NULL
```
登录后复制
值的。这样就实现了对特定条件下数据的计数。
```
SUM(CASE WHEN ... THEN ... ELSE 0 END)
```
登录后复制
也是同理，可以实现条件性求和。
```
ROLLUP
```
登录后复制
,
CUBE
登录后复制
,
GROUPING SETS
登录后复制
：这些是 SQL-92 标准引入的扩展，专门用于生成多维度的聚合报表。它们能在一个查询中同时生成多个
```
GROUP BY
```
登录后复制
组合的聚合结果，非常适合需要按不同层级汇总数据的场景。
- ```
ROLLUP
```
  登录后复制
  ：生成从最细粒度到总计的层次聚合。比如
```
GROUP BY ROLLUP(A, B)
```
  登录后复制
  会生成
```
(A, B)
```
  登录后复制
  、
```
(A)
```
  登录后复制
  和
```
()
```
  登录后复制
  （总计）三种分组组合。 场景： 统计不同地区（region）和城市（city）的销售额，并同时显示每个地区的总销售额和所有地区的总销售额。
```
SELECT
    region,
    city,
    SUM(sales_amount) AS total_sales
FROM
    sales_data
GROUP BY
    ROLLUP(region, city);
```
  登录后复制
  结果会包含
```
(region, city)
```
  登录后复制
  级别的销售额，
```
(region, NULL)
```
  登录后复制
  级别的地区总销售额，以及
```
(NULL, NULL)
```
  登录后复制
  级别的总销售额。
- ```
CUBE
```
  登录后复制
  ：生成所有可能的维度组合的聚合。
```
GROUP BY CUBE(A, B)
```
  登录后复制
  会生成
```
(A, B)
```
  登录后复制
  、
```
(A)
```
  登录后复制
  、
```
(B)
```
  登录后复制
  和
```
()
```
  登录后复制
  （总计）四种分组组合。它比
```
ROLLUP
```
  登录后复制
  更全面，但结果集也更大。
- ```
GROUPING SETS
```
  登录后复制
  ：这是最灵活的，你可以明确指定需要哪些分组组合。
```
GROUP BY GROUPING SETS((A, B), (A), (B))
```
  登录后复制
  等同于
```
CUBE(A, B)
```
  登录后复制
  。但如果我只想要
```
(A, B)
```
  登录后复制
  和
```
(B)
```
  登录后复制
  的组合，就可以写
```
GROUP BY GROUPING SETS((A, B), (B))
```
  登录后复制
  。
```
-- 示例：统计按地区-城市组合的销售额，以及单独按地区和单独按商品类型的销售额
SELECT
    region,
    city,
    product_type,
    SUM(sales_amount) AS total_sales
FROM
    sales_data
GROUP BY
    GROUPING SETS(
        (region, city),       -- 按地区和城市分组
        (region),             -- 仅按地区分组
        (product_type)        -- 仅按商品类型分组
    );
```
  登录后复制
```
GROUPING SETS
```
  登录后复制
  让我能精确控制我想要哪些聚合维度，避免了
```
CUBE
```
  登录后复制
  可能产生的过多不必要的组合，同时又比写多个
```
UNION ALL
```
  登录后复制
  查询高效得多。
窗口函数（Window Functions）： 虽然窗口函数本身不是用于“分组聚合”的，但它在“多列统计”和“灵活维度”上提供了独特的视角。它允许你在一个“窗口”（也就是一组相关的行）上执行聚合或排名操作，而不会像
```
GROUP BY
```
登录后复制
那样折叠行。这对于计算组内百分比、累计和、移动平均、排名等非常有用。

场景： 在每个客户的订单中，计算每个订单的金额占该客户总订单金额的百分比。
```
SELECT
    c.customer_name,
    o.order_id,
    o.total_amount,
    SUM(o.total_amount) OVER (PARTITION BY c.customer_id) AS customer_total_spent,
    (o.total_amount * 100.0 / SUM(o.total_amount) OVER (PARTITION BY c.customer_id)) AS percentage_of_customer_total
FROM
    customers c
INNER JOIN
    orders o ON c.customer_id = o.customer_id
ORDER BY
    c.customer_name, o.order_id;
```
登录后复制
这里的
```
SUM(o.total_amount) OVER (PARTITION BY c.customer_id)
```
登录后复制
就是一个窗口函数。它为每个客户计算了他们的总消费，但这个计算结果会附加到该客户的每一行订单数据上，而不是将所有订单折叠成一行。这使得我们可以在保留原始订单明细的同时，进行组内统计分析。