SQL:如何连接两个表?

Question

我有两个表。我需要连接它们，但我有一个疑问，因为这两个表没有一个共同的列名。

<表类> id 名称 性别 tbody><<tr>107天F145HawbakerM155汉斯道明>202BlackstonM227纵纵横道明>278Keffer来说M305快活的M329MozingoF425NolfM534沃道明>586通M618DimartinoF747BeaneM878ChatmonF904英国国会议事录F

两次联接people表，一次联接父表，一次联接子表。然后按子分组，使用条件聚合得到父和母。

select
c.name as child,
max(case when p.gender = 'M' then p.name end) as father,
max(case when p.gender = 'F' then p.name end) as mother
from relations r
join people p on p.id = r.p_id
join people c on c.id = r.c_id
group by c.id, c.name
order by c.name;

为了说明这在没有聚合的情况下也是可以实现的(同时也展示了一种初学者可能更容易想到的方法):

with fathers as
(
select p.name, r.c_id
from relations r
join people p on p.id = r.p_id
where p.gender = 'M'
)
, mothers as
(
select p.name, r.c_id
from relations r
join people p on p.id = r.p_id
where p.gender = 'F'
)
, children as
(
select c.name, c.id as c_id
from people c
where c.id in (select r.c_id from relations r)
)
select
children.name as child,
fathers.name as father,
mothers.name as mother
from children
left join mothers on mothers.c_id = children.c_id
left join fathers on fathers.c_id = children.c_id
order by children.name;

我在这里使用外连接的情况下，一个孩子的母亲或父亲是未知的(即不在关系表中)。

嵌套连接应该工作得很整齐。不需要cte或分组。

• 左连接relations…
• …内部连接具有性别F
的people
• 再次重复性别M
• 排除已经有孩子的人

select
c.name as child,
father.name as father,
mother.name as mother
from people c
left join relations rm
join people mother on mother.id = rm.p_id and mother.gender = 'F'
on rm.c_id = c.id
left join relations rf
join people father on father.id = rf.p_id and father.gender = 'M'
on rf.c_id = c.id
where c.id not in (
select r.p_id
from relations r
);

,db&lt的在小提琴