为 n:m 关系创建表

发布于 2024-12-08 00:53:12 字数 703 浏览 3 评论 0原文

我有一个关于 MySQL 的问题。我有这个表：

ID      Name
------  ------------
1       Peter
2       John
3       Carl
4       William

记录根据这个表链接：

Name          LinkedName
------------  --------------
Peter         Carl
Peter         William
Carl          John

即 - 彼得与卡尔和威廉有亲戚关系 - Carl 与 John 有关

根据该表，我如何创建这个：

ID1     ID2
------  ------------
1       3
1       4
3       2

我尝试使用两个 JOIN，例如

SELECT * 
  FROM PersonLinks T 
       JOIN Persons W 
          ON T.Name = W.Word 
       JOIN Persons W2 
          ON T.LinkedName = W2.Word;

，但我的服务器在这样的查询后没有响应。我真的希望有人能在这里帮助我。谢谢。

原文

I have a question about MySQL. I have this table:

ID      Name
------  ------------
1       Peter
2       John
3       Carl
4       William

The records are linked according to this table:

Name          LinkedName
------------  --------------
Peter         Carl
Peter         William
Carl          John

I.e.
- Peter is related to Carl and William
- Carl is related to John

Based on that table, how do I create this:

ID1     ID2
------  ------------
1       3
1       4
3       2

I have tried to use two JOINs e.g.

SELECT * 
  FROM PersonLinks T 
       JOIN Persons W 
          ON T.Name = W.Word 
       JOIN Persons W2 
          ON T.LinkedName = W2.Word;

but my server doesn't respond after such a query.
I really hope that somebody can help me here. Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

客…行舟 2024-12-15 00:53:12

考虑第一个包含 ID 和名称的表称为 Names，第二个包含关系的表称为 LinkedName。

您应该使用以下查询：

SELECT N1.ID AS ID1, N2.ID AS ID2
FROM @LinkedNames LN
LEFT OUTER JOIN @Names N1 ON N1.Name = LN.Name
LEFT OUTER JOIN @Names N2 ON N2.Name = LN.LinkedName

这已经过测试可以给出正确的结果。

Consider the first table with ID and Names is called Names and the second one with relations is called LinkedName.

You should use the following query:

SELECT N1.ID AS ID1, N2.ID AS ID2
FROM @LinkedNames LN
LEFT OUTER JOIN @Names N1 ON N1.Name = LN.Name
LEFT OUTER JOIN @Names N2 ON N2.Name = LN.LinkedName

This has been tested to give the correct result.

回复收藏 0 原文

耳钉梦 2024-12-15 00:53:12

假设第一个包含 ID 和名称的表称为 emp，第二个包含关系的表称为 link。

select emp_instance1.id,emp_instance2.id 
  from emp emp_instance1,
       emp emp_instance2,
       link link1 
 where link1.name=emp_instance1.name 
       AND link1.link=emp_instance2.name;

Suppose first table with ID and Names is called emp and the second one with relationship is called link.

select emp_instance1.id,emp_instance2.id 
  from emp emp_instance1,
       emp emp_instance2,
       link link1 
 where link1.name=emp_instance1.name 
       AND link1.link=emp_instance2.name;

回复收藏 0 原文

~没有更多了~