MySQL 多表多维度分组统计
2024-04-18 本文已影响0人
Tinyspot
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;
