MYSQL 8.0带来所有结果，包括零

Question

业务规则：计算给定部门及其子部门的打印量，即使没有打印。

MySQL 8.0版架构SQL

CREATE TABLE IF NOT EXISTS `departments` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`parent_department` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_department_department_idx` (`parent_department` ASC) VISIBLE,
CONSTRAINT `fk_department_department`
FOREIGN KEY (`parent_department`)
REFERENCES `departments` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `prints` (
`id` INT NOT NULL,
`pages` INT NOT NULL,
`copies` INT NOT NULL,
`date` DATE NOT NULL,
`department_id` INT NULL,
`printer_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_print_department1_idx` (`department_id` ASC) VISIBLE,
CONSTRAINT `fk_print_department1`
FOREIGN KEY (`department_id`)
REFERENCES `departments` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
insert into departments (id,name,parent_department)
values
(1,'Department 1',null),
(2,'Department 2',null),
(3,'Department 3',1),
(4,'Department 4',null),
(5,'Department 5',1);
insert into prints (id,pages,copies,date,department_id,printer_id)
values
(1,3,1,'2019-06-30',2,1),
(2,8,2,'2019-06-30',3,2),
(3,10,2,'2020-12-02',2,6),
(4,3,1,'2020-12-02',1,4),
(5,9,2,'2020-12-02',1,1),
(6,5,3,'2020-12-05',2,5),
(7,5,7,'2020-12-05',1,4),
(8,3,2,'2020-12-05',1,1),
(9,1,1,'2020-12-25',2,3),
(10,4,1,'2020-12-25',1,2),
(11,9,2,'2020-12-25',1,1);

查询：

SET @initial_date = '2020-12-01';
SET @final_date = '2020-12-15';
SET @department_id = 1;
WITH RECURSIVE CTE AS
(
SELECT
d1.id, d1.name, d1.parent_department
FROM
departments d1
WHERE d1.id=@department_id

UNION ALL
SELECT
d3.id, d3.name, d3.parent_department
FROM
CTE dCTE, departments d3
WHERE d3.parent_department = dCTE.id
)
SELECT
dCTE.id,
dCTE.name AS department,
d4.name AS parent_department,
COALESCE(SUM(i.copies * i.pages), 0) AS total_print,
COUNT(DISTINCT i.printer_id) AS printer_count
FROM CTE dCTE
LEFT JOIN departments d4 ON d4.id = dCTE.parent_department
LEFT JOIN prints i ON i.department_id = dCTE.id
WHERE (i.date IS NULL OR i.date BETWEEN @initial_date AND @final_date)
GROUP BY dCTE.id, dCTE.name, d4.name
ORDER BY total_print DESC, department ASC

实际结果：

id  department     parent_department   total_print  printer_count
1   Department 1          null              62            2
5   Department 5       Department 1         0             0

预期结果：

id  department     parent_department   total_print  printer_count
1   Department 1          null              62            2
3   Department 3       Department 1         0             0   <--- this is the missing row
5   Department 5       Department 1         0             0

Fiddle：https://www.db-fiddle.com/f/7FLxFS2nfyUPJdXWPRjSpF/3

问题是：由于某种原因，其中一个儿童部门没有被退回。

有什么想法吗？

感谢的帮助

Answer 1

最终它会被WHERE子句过滤掉。JOIN检测部门3记录：

页面>日期><1th>printer_id2019-06-303

名称	id	副本	1department_id
第3部门			8	2	2