在同一张桌子上加入两次的最佳方式是什么? [英] What's the best way to join on the same table twice?

问题描述

这有点复杂，但我有 2 个表.假设结构是这样的:

This is a little complicated, but I have 2 tables. Let's say the structure is something like this:

*Table1*
ID
PhoneNumber1
PhoneNumber2

*Table2*
PhoneNumber
SomeOtherField

表可以根据 Table1.PhoneNumber1 -> Table2.PhoneNumber 或 Table1.PhoneNumber2 -> Table2.PhoneNumber 进行连接.

The tables can be joined based on Table1.PhoneNumber1 -> Table2.PhoneNumber, or Table1.PhoneNumber2 -> Table2.PhoneNumber.

现在，我想获得一个结果集，其中包含 PhoneNumber1、对应于 PhoneNumber1、PhoneNumber2 的 SomeOtherField 和对应于 PhoneNumber2 的 SomeOtherField.

Now, I want to get a resultset that contains PhoneNumber1, SomeOtherField that corresponds to PhoneNumber1, PhoneNumber2, and SomeOtherField that corresponds to PhoneNumber2.

我想到了两种方法来做到这一点 - 要么在表上连接两次，要么在 ON 子句中用 OR 连接一次.

I thought of 2 ways to do this - either by joining on the table twice, or by joining once with an OR in the ON clause.

方法一:

SELECT t1.PhoneNumber1, t1.PhoneNumber2, 
   t2.SomeOtherFieldForPhone1, t3.someOtherFieldForPhone2
FROM Table1 t1
INNER JOIN Table2 t2
   ON t2.PhoneNumber = t1.PhoneNumber1
INNER JOIN Table2 t3
   ON t3.PhoneNumber = t1.PhoneNumber2

这似乎有效.

方法二:

不知何故有一个看起来有点像这样的查询 -

To somehow have a query that looks a bit like this -

SELECT ...
FROM Table1
INNER JOIN Table2 
   ON Table1.PhoneNumber1 = Table2.PhoneNumber OR
      Table1.PhoneNumber2 = Table2.PhoneNumber

我还没有让它工作，我不确定是否有办法做到这一点.

I haven't gotten this to work yet and I'm not sure if there's a way to do it.

实现这一目标的最佳方法是什么?两种方法都看起来不简单或直观......有没有更直接的方法来做到这一点?这个要求一般是如何实施的?

What's the best way to accomplish this? Neither way seems simple or intuitive... Is there a more straightforward way to do this? How is this requirement generally implemented?

推荐答案

首先，我会尝试重构这些表，以避免使用电话号码作为自然键.我不喜欢自然键，这是一个很好的例子.自然键，尤其是电话号码之类的东西，可以经常更改.在发生这种变化时更新数据库将是一个巨大的、容易出错的头痛问题.*

First, I would try and refactor these tables to get away from using phone numbers as natural keys. I am not a fan of natural keys and this is a great example why. Natural keys, especially things like phone numbers, can change and frequently so. Updating your database when that change happens will be a HUGE, error-prone headache. *

方法 1 正如您所描述的那样，它是您最好的选择.由于命名方案和短别名，它看起来有点简洁，但是......当多次加入同一个表或使用子查询等时，别名是你的朋友.

Method 1 as you describe it is your best bet though. It looks a bit terse due to the naming scheme and the short aliases but... aliasing is your friend when it comes to joining the same table multiple times or using subqueries etc.

我只想清理一下:

SELECT t.PhoneNumber1, t.PhoneNumber2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1
JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2

我做了什么:

• 无需指定 INNER - 这暗示您没有指定 LEFT 或 RIGHT
• 不要为主要查找表添加 n 后缀
• N-后缀您将多次使用的表别名以使其显而易见

*DBA 避免更新自然键的麻烦的一种方法是不指定主键和外键约束，这进一步加剧了数据库设计不佳的问题.我实际上经常看到这种情况.

*One way DBAs avoid the headaches of updating natural keys is to not specify primary keys and foreign key constraints which further compounds the issues with poor db design. I've actually seen this more often than not.

这篇关于在同一张桌子上加入两次的最佳方式是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！