主页 / 问题 / 703851
Accepted
koshachok
Asked:2020-08-09 16:55:10 +0000 UTC2020-08-09 16:55:10 +0000 UTC

从表中删除所有对

  • 772

有3张桌子

People             Sex                 Marriages

+--+----+------+   +------+--------+   +------------+-------------+
|ID|name|sex_ID|   |sex_ID|sex_name|   |First_man_ID|Second_man_ID|
+--+----+------+   +------+--------+   +------------+-------------+
|1 |Ola | 2    |   | 1    | M      |   | 1          | 2           |
|2 |Gena| 1    |   | 2    | F      |   | 1          | 3           |
|3 |Yura| 1    |   | Null | Null   |   | Null       | Null        |
+--+----+------+   +------+--------+   +------------+-------------+

如何编写 SQL 查询以选择所有相关对，即 根据表的值Marriages匹配名称。

在我们的例子中，它将是：

+-----+------+---------+----------+
|First|Second|First_sex|Second_sex|
+-----+------+---------+----------+ 
|Ola  | Gena | F       | M        |
|Ola  | Yura | F       | M        |
+-----+------+---------+----------+

PS不要专注于Olya的位置）

sql

1 个回答

  1. Best Answer
    select p1.name as First,
       p2.name as Second,
       s1.sex_name as First_sex,
       s2.sex_name as Second_sex
from Marriages m
left join People p1 on p1. id = m.First_man_ID
left join People p2 on p2. id = m.Second_man_ID
left join Sex s1 on s1.sex_ID = p1.sex_ID
left join Sex s2 on s2.sex_ID = p2.sex_ID
where m.First_man_ID is not null
    • 2

相关问题