Laravel如何通过第三张关联表对数据进行分类统计

需求说明 #

基于categories、books、transactions三张关联表，按分类统计对应书籍的交易总金额，例如统计novel分类的交易总金额应为$50。

表结构回顾 #

categories表

+----+---------+
| id |  name   |
+----+---------+
|  1 |  novel  |
|  2 |  comics |
+----+---------+

books表

+----+--------------+------------------+---------+
| id |  title       |    category_id   |  price  |
+----+--------------+------------------+---------+
|  1 |  Don Quixote |     1            |   $10.  |
|  2 |  Naruto      |     2            |   $5.   | 
|  3 |  One Piece   |     2            |   $5.   |
+----+--------------+------------------+---------+

transactions表

+----+--------------+--------------+---------+
| id |     book_id  |    pcs       |  total  |
+----+--------------+--------------+---------+
|  1 |  1           |     3        |   $30.  |
|  2 |  1           |     2        |   $20.  |
|  3 |  2           |     2        |   $10.  |
|  4 |  3           |     2        |   $10.  |
+----+--------------+--------------+---------+

解决方案 #

1. 单分类统计（以novel为例）

使用INNER JOIN关联三张表，通过分类名称筛选后求和交易总金额：

SELECT 
    c.name AS category_name,
    SUM(t.total) AS total_transaction_amount
FROM 
    categories c
INNER JOIN 
    books b ON c.id = b.category_id
INNER JOIN 
    transactions t ON b.id = t.book_id
WHERE 
    c.name = 'novel'
GROUP BY 
    c.name;

执行结果：

+---------------+-------------------------+
| category_name | total_transaction_amount|
+---------------+-------------------------+
| novel         | $50.                    |
+---------------+-------------------------+

2. 全分类统计

如果需要一次性统计所有分类的交易总金额，去掉WHERE条件即可：

SELECT 
    c.name AS category_name,
    SUM(t.total) AS total_transaction_amount
FROM 
    categories c
INNER JOIN 
    books b ON c.id = b.category_id
INNER JOIN 
    transactions t ON b.id = t.book_id
GROUP BY 
    c.name;

执行结果：

+---------------+-------------------------+
| category_name | total_transaction_amount|
+---------------+-------------------------+
| novel         | $50.                    |
| comics        | $20.                    |
+---------------+-------------------------+

逻辑说明 #

• INNER JOIN确保只关联存在对应关系的数据：分类关联对应书籍，书籍关联对应交易记录
• SUM(t.total)对同一分类下所有交易的total字段求和
• GROUP BY c.name按分类名称分组统计，保证每个分类只返回一条汇总结果

内容的提问来源于stack exchange，提问作者shinichirou ikebe