多表关联SQL查询需求：统计含抵扣的发票总项目数

嘿，我来帮你搞定这个SQL查询的问题！咱们一步步来拆解：

数据表结构与数据 #

首先，你提供的三张表的建表和插入数据SQL如下：

CREATE TABLE `invoice` ( `id` int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `invoice` (`id`) VALUES (1), (2), (3);

CREATE TABLE `invoice_deduction` ( `id` int(11) NOT NULL, `invoiceId` int(11) NOT NULL, `deductionId` int(11) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (`invoiceId`) REFERENCES `invoice` (`id`), FOREIGN KEY (`deductionId`) REFERENCES `invoice` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `invoice_deduction` (`id`, `invoiceId`, `deductionId`) VALUES (1, 2, 1), (2, 3, 1), (3, 3, 2);

CREATE TABLE `invoice_item` ( `id` int(11) NOT NULL, `invoiceId` int(11) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (`invoiceId`) REFERENCES `invoice` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `invoice_item` (`id`, `invoiceId`) VALUES (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 3), (8, 3);

业务逻辑示例 #

你给出的计算规则很清晰：

• 第1张发票：仅自身的3个项目 → 总计3个
• 第2张发票：自身2个项目 + 第1张的3个抵扣项目 → 总计5个
• 第3张发票：自身3个项目 + 第2张的2个抵扣项目 + 第1张的3个抵扣项目 → 总计8个

需求目标 #

需要得到如下格式的查询结果：

你的初始查询 #

你当前的查询语句只能统计每张发票自身的项目数，没有包含抵扣关联的发票项目：

SELECT i.id, COUNT(*) as countItems FROM invoice i JOIN invoice_item it ON i.id = it.invoiceId GROUP BY it.invoiceId ORDER BY countItems DESC

解决方案：递归CTE实现关联统计 #

要实现包含所有抵扣链的项目统计，我们可以用**递归CTE（Common Table Expression）**来遍历每张发票及其所有直接/间接抵扣的发票，然后统一统计这些发票的项目总数：

WITH RECURSIVE invoice_hierarchy AS (
    -- 第一步：初始化，每张发票先关联自己
    SELECT id AS invoice_id, id AS related_invoice
    FROM invoice
    UNION ALL
    -- 第二步：递归遍历，找出每张发票所有直接/间接抵扣的发票
    SELECT ih.invoice_id, id.deductionId AS related_invoice
    FROM invoice_hierarchy ih
    JOIN invoice_deduction id ON ih.related_invoice = id.invoiceId
)
-- 第三步：统计所有关联发票的项目总数
SELECT 
    ih.invoice_id AS id,
    COUNT(ii.id) AS `count of items (with deductions)`
FROM invoice_hierarchy ih
JOIN invoice_item ii ON ih.related_invoice = ii.invoiceId
GROUP BY ih.invoice_id
ORDER BY `count of items (with deductions)` DESC;

逻辑解释 #

1. 递归CTE invoice_hierarchy：
   • 初始部分：为每个发票创建一条自身关联的记录（比如发票1对应related_invoice 1）
   • 递归部分：不断查找当前关联发票所抵扣的发票（比如发票2抵扣了1，所以会添加发票2对应related_invoice 1；发票3抵扣了2和1，所以会添加发票3对应related_invoice 2，再进一步找到related_invoice 1）
2. 统计阶段：把所有关联的发票和invoice_item关联，统计每个invoice_id对应的所有项目数，最后按总数降序排序，就得到了你要的结果。

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