MySQL SQL 从多个表中选择列而不重复数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8320849/
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
SQL SELECT Columns From Multiple Tables Without Repeating Data
提问by ryandlf
I am trying to query 2 tables using the following sql statement in an attempt to return all records from each table that contains a specific id.
我正在尝试使用以下 sql 语句查询 2 个表，以尝试从包含特定 id 的每个表中返回所有记录。
SELECT Phone.Phone, Email.Email FROM Contacts.Phone, Contacts.Email
WHERE Phone.ContactId = :contactId AND Email.ContactId = :contactId
Contacts.Phonetable contains 2 phone numbers for the given id and Contacts.Emailcontains 1 email for the given id. Using the above sql query, I get the following rows returned. This is just an example, of course, of a situation in where my result set from each table does match in number of rows.
Contacts.Phone表包含给定 ID 的 2 个电话号码，并Contacts.Email包含给定 ID 的1 个电子邮件。使用上面的 sql 查询，我得到以下行返回。当然，这只是一个例子，其中每个表的结果集在行数上确实匹配。
Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - [email protected]
The email is repeated in order to fill in the second row when I am trying to get:
当我尝试获取以下内容时，会重复发送电子邮件以填写第二行：
Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - NULL
I think I need to use a UNIONto somehow join the tables, but I can't figure out exactly how to write the sql statement. Another option would be to perform 2 separate SQL queries, which would be easier but I figure performance wise it would be better to collect all data I need in one query.
我想我需要使用 aUNION以某种方式连接表，但我无法弄清楚如何编写 sql 语句。另一种选择是执行 2 个单独的 SQL 查询，这会更容易，但我认为在性能方面最好在一个查询中收集我需要的所有数据。
I am using MySQL.
我正在使用 MySQL。
采纳答案by ruakh
First of all, I think this:
首先，我认为：
Another option would be to perform 2 separate SQL queries, which would be easier but I figure performance wise it would be better to collect all data I need in one query.
另一种选择是执行 2 个单独的 SQL 查询，这会更容易，但我认为在性能方面最好在一个查询中收集我需要的所有数据。
is a bit misguided. The performance difference between one complex query and two simple queries will be quite small. That said, you suggested using a UNION; if you do want to use a single query, you can do this:
有点误导。一个复杂查询和两个简单查询之间的性能差异将非常小。也就是说，您建议使用UNION; 如果您确实想使用单个查询，您可以这样做：
SELECT 'EMAIL', Email.Email
FROM Contacts.Email
WHERE Email.ContactId = :contactId
UNION ALL
SELECT 'PHONE', Phone.Phone
FROM Contacts.Phone
WHERE Phone.ContactId = :contactId
ORDER BY 1 -- put e-mail addresses before phone-numbers
;
The first column will return the "type" of result, and the second column will have the datum.
第一列将返回结果的“类型”，第二列将包含数据。
回答by John W. Mnisi
Use a left outer jointo solve your problem, the query would look sonething like this if you were using MS SQL:
使用 aleft outer join来解决您的问题，如果您使用 MS SQL，查询将看起来像这样：
SELECT
Phone.Phone,
Email.Email
FROM
Contacts.Phone
Left Outer Join Contacts.Email ON Phone.ContactId = Email.ContactId
Your results will look like the following:
您的结果将如下所示：
Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - NULL
回答by srgerg
If you really wanted the results in two columns with NULLs for repeated data, then it would really help if MySQL supported the RANK() OVER (PARTITION BY ...syntax that some other DBMS systems allow. Regrettably it does not, but Daniel Vassallo has come to the rescue by describing how it can be done in MySQL in this question.
如果您真的希望在两列中得到重复数据为 NULL 的结果，那么如果 MySQL 支持RANK() OVER (PARTITION BY ...某些其他 DBMS 系统允许的语法，那将非常有帮助。遗憾的是它没有，但 Daniel Vassallo 在这个问题中描述了如何在 MySQL 中完成它来拯救它。
Adapting his approach to your situation:
根据你的情况调整他的方法：
SELECT Phone.Phone,
CASE Email.Email
WHEN @curEmail THEN NULL
ELSE @curEmail := Email.Email END AS Email
FROM Contacts.Phone, Contacts.Email, (SELECT @curEmail := '') AS r
WHERE Phone.ContactId = :contactId AND Email.ContactId = :contactId
When I ran this on the test data in your question, I got:
当我在你的问题中的测试数据上运行这个时，我得到：
Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - NULL
which is the desired result.
这是想要的结果。
