By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `department` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`father` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_department_department_idx` (`father` ASC) VISIBLE,
CONSTRAINT `fk_department_department`
FOREIGN KEY (`father`)
REFERENCES `department` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `print` (
`id` INT NOT NULL,
`page` INT NOT NULL,
`copy` INT NOT NULL,
`date` DATE NOT NULL,
`department` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_print_department1_idx` (`department` ASC) VISIBLE,
CONSTRAINT `fk_print_department1`
FOREIGN KEY (`department`)
REFERENCES `department` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
insert into department (id,name,father)
values
(1, 'dp1',null),
(2, 'dp2',null),
(3, 'dp3',1),
(4, 'dp4',2);
insert into print (id,page,copy,date,department)
Records: 4 Duplicates: 0 Warnings: 0
Records: 8 Duplicates: 0 Warnings: 0
with
d as (
select d.id, d.name as department, f.name as father
from department d left join department f
on f.id = d.father
),
p as (
select department, sum(page*copy) as sum_print
from print
where date between CAST('2020-1-13' AS DATE) AND CAST('2020-1-15' AS DATE)
group by department
)
select d.department, d.father, p.sum_print
from d left join p
on p.department = d.id
union all
select d.department, d.father, p.sum_print
from d right join p
on p.department = d.id
where d.id is null
| department | father | sum_print |
|---|---|---|
| dp1 | null | 24 |
| dp2 | null | null |
| dp3 | dp1 | null |
| dp4 | dp2 | null |
| null | null | 35 |