mysql - 加入时选择空值的Mysql
问题描述
我有以下 MySQL 表(创建和插入):
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)
values
(1,2,3,'2020-1-11',1),
(2,1,6,'2020-1-12',4),
(3,1,6,'2020-1-12',1),
(4,4,5,'2020-1-13',null),
(5,5,3,'2020-1-15',null),
(6,3,4,'2020-1-15',1),
(7,3,4,'2020-1-15',1),
(8,2,2,'2020-1-16',4);
我得到的最接近的是以下查询。
select
d.name as department,
f.name as father,
sum_print as sum
from
department d
left join department f on f.id = d.father
left join
(
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
) as tmp on tmp.department = d.id;
我得到的结果是。
| department | father | sum |
| dp1 | null | 24 |
| dp2 | null | null |
| dp3 | dp1 | null |
| dp4 | dp2 | null |
我需要的是以下内容。
| department | father | sum |
| dp1 | null | 24 |
| dp2 | null | null |
| dp3 | dp1 | null |
| dp4 | dp2 | null |
| null | null | 35 |
由于某种我无法弄清楚的原因,我错过了最后一个元组。我想是因为这个加入as tmp on tmp.department = d.id
为任何想要使用它和/或可以提供帮助的人关注小提琴:小提琴
我尝试了以下帖子,但没有运气。我现在卡住了。
解决方案
您实际需要的是 2 个查询的完全外连接。
问题是MySql(仍然)不支持这种join,所以必须用left和right join和union all来模拟:
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
推荐阅读
- html - 如何从下拉菜单图标/链接/图像中删除点击轮廓?
- android - 清单未解决的类
- .net - 如何防止在 Telerik Spreadsheet .NET 中将公式转换为文本
- android - 如何修复我的代码以防止它不响应我的点击/触摸,为什么它有时只显示一个数字?
- c# - 如何使用身份 2.0 使用授权属性添加多个策略?
- deployment - 如何在 Kubernetes 中相同部署的两个 Pod 中使环境变量不同?
- javascript - 正则表达式在函数中是如何工作的?
- javascript - 时刻是 2 次之间的时间,无论日期如何
- python-3.x - 更改 keras 模型输入名称
- unity3d - 为什么在 Unity 中只有 Mono 显示为 PC、Mac 和独立平台的脚本后端?