SQL 是否可以在选择查询中两次使用同一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7383753/
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
Is it possible to use the same table twice in a select query?
提问by locoboy
Hi I have the following query, and I'm wondering what it means:
您好，我有以下查询，我想知道这意味着什么：
SELECT c1.id as sender, c2.id as replier
FROM contacts c1, contacts c2;
How can you use the same table twice?
你怎么能两次使用同一张桌子？
采纳答案by Ray Toal
This query creates a table containing all possible pairs of contact ids.
此查询创建一个包含所有可能的联系人 ID 对的表。
For example, if your contact ids were 1, 2, and 3 you would get, as a result
例如，如果您的联系人 ID 为 1、2 和 3，您将得到，结果
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
回答by Jonathan Leffler
You use a single table twice in a query by giving it two names, like that.
通过给它两个名称，您可以在查询中两次使用单个表，就像这样。
The aliases are often introduced with the keyword AS. You also normally specify a join condition (for without it, you get the Cartesian Productof the table joined with itself). For preference you use the explicit JOIN notation.
别名通常用关键字 AS 引入。您通常还指定连接条件（因为没有它，您将获得与自身连接的表的笛卡尔积）。优先使用显式 JOIN 表示法。
SELECT c1.id AS sender, c2.id AS replier
FROM contacts AS c1
JOIN contacts AS c2 ON c1.xxx = c2.yyy;
It is not clear which columns might be used for the join in this example; we don't have any information to help resolve that.
在此示例中，不清楚哪些列可用于连接；我们没有任何信息可以帮助解决这个问题。
Normally, there'd be another table to act as intermediary, such as a Messages table:
通常，会有另一个表作为中介，例如 Messages 表：
SELECT c1.id AS sender, c1.email AS sender_email,
c2.id AS replier, c2.email AS replier_email,
m.date_time
FROM messages AS m
JOIN contacts AS c1 ON m.sender_id = c1.id
JOIN contacts AS c2 ON m.replier_id = c2.id;
回答by gion_13
It's a simple answer : use your query listed in the example. It should work just fine. Although this is probably a bad idea, if you want to use the same table twice, be aware that you have to join "those" tables as they were different :
这是一个简单的答案：使用示例中列出的查询。它应该工作得很好。虽然这可能是一个坏主意，但如果您想两次使用同一张表，请注意您必须连接“那些”表，因为它们是不同的：
SELECT c1.id as sender, c2.id as replier
FROM contacts c1, contacts c2
WHERE sender.id = replier.id
回答by Pearl
Yes, you can use the same table twice by giving different aliases to the table. I think studying about self joins will help you understand.
是的，您可以通过为表提供不同的别名来两次使用同一个表。我认为学习自联接会帮助你理解。
回答by onedaywhen
Yes, you can use the same table more than once within the same SELECTquery.
是的，您可以在同一个SELECT查询中多次使用同一个表。
Note that you only need to use table correlation names (colloquially 'aliases') when the table appears more than once within the same scope. For example, the following SELECTquery uses the same table twice but, because each is within a distinct scope (each separated by the UNIONkeyword), no table correlation name is required:
请注意，当表在同一范围内出现多次时，您只需要使用表相关名称（通俗地称为“别名”）。例如，以下SELECT查询两次使用同一个表，但由于每个都在不同的范围内（每个都由UNION关键字分隔），因此不需要表相关名称：
SELECT id, 'Sender' AS contact_narrative
FROM contacts
WHERE something = 1
UNION
SELECT id, 'Replier' AS contact_narrative
FROM contacts
WHERE something = 2;
