gpt4 book ai didi

mysql - SQL 选择语句 : One Child - Two Parents (two different FK)

转载 作者:太空宇宙 更新时间:2023-11-03 10:32:24 25 4
gpt4 key购买 nike

我遇到的情况最好通过以下场景来解释:

  • Child 记录有两个 Parent(母亲和父亲)

如果您稍后阅读这篇文章时考虑应用自引用表 场景的选项,则没有必要,因为没有祖父或孙子。

关于 parent 它是如何定义的:

CREATE TABLE IF NOT EXISTS parent(
code varchar(3),
name varchar(10),
PRIMARY KEY(code)
)ENGINE=INNODB;

记录是如何插入的:

INSERT INTO parent(code,name) VALUES('001','Mary');
INSERT INTO parent(code,name) VALUES('002','Joseph');
INSERT INTO parent(code,name) VALUES('003','Adan');
INSERT INTO parent(code,name) VALUES('004','Eva');
INSERT INTO parent(code,name) VALUES('005','Ana');
INSERT INTO parent(code,name) VALUES('006','Elcana');

select 查询按预期工作:

mysql> select * from parent;
+------+--------+
| code | name |
+------+--------+
| 001 | Mary |
| 002 | Joseph |
| 003 | Adan |
| 004 | Eva |
| 005 | Ana |
| 006 | Elcana |
+------+--------+
6 rows in set (0.01 sec)

关于 child ,它是如何定义的:

CREATE TABLE IF NOT EXISTS child(
code varchar(3),
name varchar(10),
PRIMARY KEY(code),
mother_code varchar(3),
father_code varchar(3),
FOREIGN KEY fk_mother_code(mother_code) REFERENCES parent(code),
FOREIGN KEY fk_father_code(father_code) REFERENCES parent(code)
)ENGINE=INNODB;

观察:从上面观察期望两个PK来自 code>(假设必须不同)通过两个 FKs。

记录是如何插入的:

INSERT INTO child(code, name, mother_code, father_code) VALUES('001','Jesus', '001', '002');
INSERT INTO child(code, name, mother_code, father_code) VALUES('002','Cain', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('003','Abel', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('004','Set', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('005','Samuel', '005', '006');

select 查询按预期工作:

mysql> select * from child;
+------+--------+-------------+-------------+
| code | name | mother_code | father_code |
+------+--------+-------------+-------------+
| 001 | Jesus | 001 | 002 |
| 002 | Cain | 003 | 004 |
| 003 | Abel | 003 | 004 |
| 004 | Set | 003 | 004 |
| 005 | Samuel | 005 | 006 |
+------+--------+-------------+-------------+
5 rows in set (0.00 sec)

目标是获得以下内容:

+------+--------+------+-------+-------------+-------------+
| code | name | code | name | mother_code | father_code |
+------+--------+------+-------+-------------+-------------+
| 001 | Mary | 001 | Jesus | 001 | 002 |
| 002 | Joseph | 001 | Jesus | 001 | 002 |
+------+--------+------+-------+-------------+-------------+

我尝试了以下方法:

SELECT p.*, c.* FROM parent p,
child c,
(SELECT pm.code AS m_code FROM parent pm) AS m,
(SELECT pf.code AS f_code FROM parent pf) AS f
WHERE
m.m_code='001' AND
f.f_code='002' AND
c.mother_code=m.m_code AND
c.father_code=f.f_code AND
c.mother_code='001' AND
c.father_code='002' AND
c.code='001';

where 子句看起来多余,这是因为我试图获得所需的结果,因此它包含编写正确查询的尝试。

但总是返回:

+------+--------+------+-------+-------------+-------------+
| code | name | code | name | mother_code | father_code |
+------+--------+------+-------+-------------+-------------+
| 001 | Mary | 001 | Jesus | 001 | 002 |
| 002 | Joseph | 001 | Jesus | 001 | 002 |
| 003 | Adan | 001 | Jesus | 001 | 002 |
| 004 | Eva | 001 | Jesus | 001 | 002 |
| 005 | Ana | 001 | Jesus | 001 | 002 |
| 006 | Elcana | 001 | Jesus | 001 | 002 |
+------+--------+------+-------+-------------+-------------+
6 rows in set (0.00 sec)

那么正确的句子是什么?

最佳答案

你只是在寻找两个 join 吗?

select c.*, pm.name as mother_name, pf.name as father_name
from child c join
parent pm
on c.mother_code = pm.code join
parent pf
on c.father_code = pf.code;

您可以添加一个 where 子句来将其过滤到特定的子项:

where c.code in ('001', '002')

关于mysql - SQL 选择语句 : One Child - Two Parents (two different FK),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55600195/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com