MySQL 多表多维度分组统计

1. 多表分组统计

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

1.1 表数据

表 boot_order 数据

code	tradeId
1001	20240301001
1002	20240301002
1003	20240301003

表 boot_indicator 数据

tradeId	quantity	tpCode	mailNo
20240301001	1	SF	SF001
20240301001	5	ZTO	ZTO001
20240301001	4	ZTO	ZTO002
20240301002	2	SF	SF001
20240301003	3	SF	SF003
20240301003	4	STO	STO001

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;

code	tradeId	quantity	tpCode	mailNo
1001	20240301001	1	SF	SF001
1001	20240301001	5	ZTO	ZTO001
1001	20240301001	4	ZTO	ZTO002
1002	20240301002	2	SF	SF001
1003	20240301003	3	SF	SF003
1003	20240301003	4	STO	STO001

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;

code	total
1001	3
1002	1
1003	2

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;

code	tpCode	quantities	total
1001	SF	1	1
1002	SF	2	1
1003	SF	3	1
1003	STO	4	1
1001	ZTO	9	2

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;