MySQL 多表多维度分组统计

1. 多表分组统计

多表的数据进行 GROUP BY 操作时，通常涉及到多表连接（JOIN），以便将相关数据合并到一起进行分组

1.1 表数据

表 boot_order 数据

表 boot_indicator 数据

1.2 JOIN 合并表数据

JOIN ON 实际为 INNER JOIN ON

SELECT o.code, t.tradeId, t.quantity, t.tpCode, t.mailNo
FROM boot_order o
JOIN boot_indicator t ON t.tradeId = o.tradeId
ORDER BY o.code;

1.3 group by 单字段

SELECT o.code, count(1) as total
FROM boot_order o
JOIN boot_indicator t ON t.tradeId = o.tradeId
GROUP BY o.code;

2. 子查询方式

SELECT o.code, t.tpCode, sum(t.quantity) as quantities, count(1) as total
FROM boot_order o
JOIN boot_indicator t ON t.tradeId = o.tradeId
GROUP BY o.code, t.tpCode;

3. WITH 子句

WITH temp AS (
    SELECT o.code, t.tradeId, t.quantity, t.tpCode, t.mailNo
    FROM boot_order o
    JOIN boot_indicator t ON t.tradeId = o.tradeId
)
SELECT code, tpCode, sum(quantity) as quantities, count(1) as total
FROM temp
GROUP BY code, tpCode;

4. 临时表

DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp AS
SELECT o.code, t.tradeId, t.quantity, t.tpCode, t.mailNo
FROM boot_order o
JOIN boot_indicator t ON t.tradeId = o.tradeId;

SELECT code, tpCode, sum(quantity) as quantities, count(1) as total
FROM temp
GROUP BY code, tpCode;

【信息由网络或者个人提供，如有涉及版权请联系COOY资源网邮箱处理】