SQL选择从两个表中的数据（一列 - ＆GT;多行） [英] SQL Select data from two tables (one row -> multiple rows)

问题描述

假设我有两个SQL表：客户还有联系电话

Suppose I have two SQL tables: Customers and PhoneNumbers.

假设客户有以下栏目：客户ID（主键），FNAME，LNAME

Suppose that Customers has the following columns: customerId (primary key), fName, lName.

假设PhoneNumbers有以下栏目：phoneNumberId（主键），phoneNumber的，客户ID（外键）

Suppose that PhoneNumbers has the following columns: phoneNumberId (primary key), phoneNumber, customerId (foreign key).

我至今不解的是，如果每个客户都有一个电话号码，我可以选择每个客户用下面的SQL的FNAME，LNAME，和PhoneNumber：

What I understand so far is that if each customer has one phone number, I can select the fName, lName, and phoneNumber of each customer with the following SQL:

SELECT 
    customer.fName, customer.lName, phone.phoneNumber 
FROM 
    Customers customer 
        INNER JOIN phoneNumbers phone ON 
            customer.customerId = phone.customerId

如果一个客户可能有多个电话号码？我如何得到客户的列表，每个客户的电话号码列表？

What if a customer may have more than one phone number? How do I get a list of customers with the list of phone numbers of each customer?

我的编程语言来驱动SQL是C＃/。NET。

My programming language to drive the SQL is C#/.NET.

推荐答案

正如你所说，如果有确切的每个客户各占联系号码，你的查询将正常工作。

As you say, if there is exactly one PhoneNumber per customer, your query will work.

与多个电话号码的客户也将被退回，但客户记录将被复制为每个不同的电话号码。

Customers with more than one Phone Number will also be returned, but the customer record will be duplicated for each different phone number.

您需要考虑的另一个条件是客户，没有电话号码。如果INNER JOIN的表，那么这些客户将被排除在结果集。要包括这样的客户，你需要使用外连接。

The other condition you need to consider is customers with no phone numbers. If you INNER JOIN the tables, then these customers will be excluded from the result set. To include such customers, you need to use an OUTER JOIN.

这篇关于SQL选择从两个表中的数据（一列 - ＆GT;多行）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！