SQL 对同一个表中的多列执行内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2798317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performing Inner Join for Multiple Columns in the Same Table
提问by Frankie Simon
I have a scenario which I'm a bit stuck on. Let's say I have a survey about colors, and I have one table for the color data, and another for people's answers.
我有一个场景，我有点坚持。假设我有一个关于颜色的调查，我有一个颜色数据表，另一个用于人们的答案。
tbColors
颜色
color_code , color_name
1 , 'blue'
2 , 'green'
3 , 'yellow'
4 , 'red'
tbAnswers
回答
answer_id , favorite_color , least_favorite_color , color_im_allergic_to
1 , 1 , 2 3
2 , 3 , 1 4
3 , 1 , 1 2
4 , 2 , 3 4
For display I want to write a SELECT that presents the answers table but using the color_name column from tbColors.
对于显示，我想编写一个 SELECT 来显示答案表，但使用 tbColors 中的 color_name 列。
I understand the "most stupid" way to do it: naming tbColors three times in the FROM section, using a different alias for each column to replace.
我理解“最愚蠢”的方法：在 FROM 部分命名 tbColors 三次，为每一列使用不同的别名来替换。
How would a non-stupid way look?
一个不愚蠢的方式会是什么样子？
回答by BradBrening
This seems like the way to go:
这似乎是要走的路：
SELECT
A.answer_id
,C1.color_name AS favorite_color_name
,C2.color_name AS least_favorite_color_name
,C3.color_name AS color_im_allergic_to_name
FROM tbAnswers AS A
INNER JOIN tbColors AS C1
ON A.favorite_color = C1.color_code
INNER JOIN tbColors AS C2
ON A.least_favorite_color = C2.color_code
INNER JOIN tbColors AS C3
ON A.color_im_allergic_to = C3.color_code
Rather than "stupid", I'd venture that this is a pretty standard query. This also presumes that all columns will have a valid value. Otherwise, replace all INNER JOINs with LEFT JOINs
与其说“愚蠢”，不如说这是一个非常标准的查询。这还假定所有列都将具有有效值。否则，将所有 INNER JOIN 替换为 LEFT JOIN
