postgreSQL 查询，当 WHERE 不匹配时返回具有某些 NULL 值的记录

Question

我在Postgres中运行以下查询：

SELECT  raw_times.*, efforts.id as effort_id, efforts.event_id as event_id, splits.id as split_id 
FROM raw_times 
INNER JOIN event_groups ON event_groups.id = raw_times.event_group_id 
INNER JOIN events ON events.event_group_id = event_groups.id 
INNER JOIN efforts ON efforts.event_id = events.id 
INNER JOIN aid_stations ON aid_stations.event_id = events.id 
INNER JOIN splits ON splits.id = aid_stations.split_id 
WHERE efforts.bib_number::text = raw_times.bib_number
AND splits.parameterized_base_name = raw_times.parameterized_split_name

这个想法是找到匹配的号码布号码和拆分名称，并返回填充了各种关系 ID 的raw_time记录。

用简单的英语来说，逻辑是这样的：对于每个raw_time，检查event_group_id。一个event_group有许多事件，一个事件有很多努力，努力表有一个bib_number列。号码布编号在event_group中是唯一的，但在整个努力表中不是唯一的。

因此，对于每个raw_time，因为我们知道event_group_id和bib_number，我们可以确定它与哪些努力有关。了解努力也可以让我们了解事件(因为努力有event_id)。

一个事件通过aid_stations联接表进行了多次拆分。拆分名称在事件中是唯一的。因为我们知道事件(如上所述确定)并且我们知道拆分名称(它是raw_times表上的一列)，所以我们可以确定split_id。

对于具有匹配号码布编号和拆分名称的记录，查询按预期工作。但是对于号码布编号或拆分名称不匹配的记录，不满足 WHERE 子句，因此根本不返回raw_time记录。

我已经尝试使用左联接代替每个内联接进行查询，但我得到了相同的结果。

我想要的是返回所有raw_time记录，但是如果没有匹配的拆分名称，则返回split_id的 NULL 记录，如果没有匹配的号码布编号，则返回带有 NULL 的记录，用于effort_id、event_id和split_id。

raw_times表如下所示：

id  event_group_id  parameterized_split_name  bib_number
3        53         finish                    11
4        53         finish                    603
5        53         finish                    9999
6        53         nonexistent               603

event_groups表如下所示：

id
53
51

事件表如下所示：

id  event_group_id
26  53
28  53
18  51

工作量表如下所示：

id      event_id  bib_number
22183   26        11
22400   28        603
5747    18        11

aid_stations表如下所示：

id   event_id  split_id
236  26        30
237  26        31
238  26        106
239  26        111
240  26        112
241  26        109
242  26        113
254  28        119
255  28        118
138  18        1
150  18        16

拆分表如下所示：

id  parameterized_base_name
30  finish
31  start
106 aid-1
109 aid-4
111 aid-2
112 aid-3
113 aid-5
118 start
119 finish
1   start
16  finish

查询应返回以下内容：

id  event_group_id parameterized_split_name bib_number effort_id event_id  split_id
3   53             finish                   11         22183     26        30
4   53             finish                   603        22400     28        119
6   53             nonexistent              603        22400     28        NULL
5   53             finish                   9999       NULL      NULL      NULL

这是ERD的链接：https://github.com/SplitTime/OpenSplitTime/blob/master/erd.pdf

Answer 1

通过拥有样本数据和所需结果的优势，"缺少元素"似乎是需要通过split_id的相关子查询获得有限的结果。

在PostgreSQL 9.6中查看此工作 rextester.com

CREATE TABLE raw_times
(id int, event_group_id int, parameterized_split_name varchar(11), bib_number int)
;
INSERT INTO raw_times
(id, event_group_id, parameterized_split_name, bib_number)
VALUES
(3, 53, 'finish', 11),
(4, 53, 'finish', 603),
(5, 53, 'finish', 9999),
(6, 53, 'nonexistent', 603)
;

CREATE TABLE event_groups
(id int)
;
INSERT INTO event_groups
(id)
VALUES
(53)
;

CREATE TABLE efforts
(id int, event_id int, bib_number int)
;
INSERT INTO efforts
(id, event_id, bib_number)
VALUES
(22183, 26, 11),
(22400, 28, 603)
;

CREATE TABLE aid_stations
(id int, event_id int, split_id int)
;
INSERT INTO aid_stations
(id, event_id, split_id)
VALUES
(236, 26, 30),
(237, 26, 31),
(238, 26, 106),
(239, 26, 111),
(240, 26, 112),
(241, 26, 109),
(242, 26, 113),
(254, 28, 119),
(255, 28, 118)
;

CREATE TABLE splits
(id int, parameterized_base_name varchar(6))
;
INSERT INTO splits
(id, parameterized_base_name)
VALUES
(30, 'finish'),
(31, 'start'),
(106, 'aid-1'),
(109, 'aid-4'),
(111, 'aid-2'),
(112, 'aid-3'),
(113, 'aid-5'),
(118, 'start'),
(119, 'finish')
;

查询 1：

select 
r.id, r.event_group_id, r.parameterized_split_name, r.bib_number
, e.id as effort_id
, e.event_id
, s.split_id
from raw_times r
left join (
select ef.id, ef.event_id, ef.bib_number, ev.event_group_id
from efforts ef
inner join events ev on ef.event_id = ev.id
) e on r.bib_number = e.bib_number
and e.event_group_id = r.event_group_id
left join lateral (
select a.split_id from aid_stations a
inner join splits s on a.split_id = s.id
where a.event_id = e.event_id
and s.parameterized_base_name = r.parameterized_split_name
limit 1) s on true
order by r.bib_number, r.id
;

结果：

| id | event_group_id | parameterized_split_name | bib_number | effort_id | event_id | split_id |
|----|----------------|--------------------------|------------|-----------|----------|----------|
|  3 |             53 |                   finish |         11 |     22183 |       26 |       30 |
|  4 |             53 |                   finish |        603 |     22400 |       28 |      119 |
|  6 |             53 |              nonexistent |        603 |     22400 |       28 |   (null) |
|  5 |             53 |                   finish |       9999 |    (null) |   (null) |   (null) |

注意。如果使用旧版本的 Postgres，则可以在 select 子句中使用相关子查询来代替上面看到的left join lateral。

Answer 2

在这种情况下，请在执行左外连接时添加条件。

SELECT  raw_times.*, efforts.id as effort_id, efforts.event_id as event_id, splits.id as split_id 
FROM raw_times 
INNER JOIN event_groups ON event_groups.id = raw_times.event_group_id 
INNER JOIN events ON events.event_group_id = event_groups.id 
LEFT JOIN efforts ON efforts.event_id = events.id AND efforts.bib_number::text = raw_times.bib_number
INNER JOIN aid_stations ON aid_stations.event_id = events.id 
LEFT JOIN splits ON splits.id = aid_stations.split_id AND splits.parameterized_base_name = raw_times.parameterized_split_name

编辑：

SELECT  raw_times.*, efforts.id as effort_id, efforts.event_id as event_id, splits.id as split_id 
FROM raw_times 
INNER JOIN event_groups ON event_groups.id = raw_times.event_group_id 
INNER JOIN events ON events.event_group_id = event_groups.id 
LEFT JOIN efforts ON efforts.event_id = events.id 
INNER JOIN aid_stations ON aid_stations.event_id = events.id 
LEFT JOIN splits ON splits.id = aid_stations.split_id 
WHERE (efforts.bib_number::text = raw_times.bib_number OR efforts.event_id IS NULL)
AND (splits.parameterized_base_name = raw_times.parameterized_split_name OR splits.id IS NULL)