与 LEFT JOINS 相关的 MySQL 子查询

Question

我接到了从数据库中提取客户信息的任务，但我卡在了最后一部分!我希望我的解释足以描述我的问题和尝试。

目标:为每位客户返回一行及其所有电话号码

问题:每个客户可能有很多电话号码

尝试:

SUBQUERY:出现“子查询返回超过 1 行”错误。这对我来说很有意义，但我不能为每个客户插入 WHERE 语句

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE,
(SELECT telephone.Number 
    FROM customer
    LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
    LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID 
    WHERE telephone.Type = "Main") as MainPhone
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LIMIT 100

LEFT JOIN:查询返回一行/客户/数字，但我需要一行中的每个数字。

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, telephone.Number
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID 
LIMIT 100

GROUP BY:查询正确地为每个客户返回一行，但只返回第一个数字。

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, telephone.Number
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID
LIMIT 100

我如何才能为每位客户返回一行，仅在一行中显示他们的每个电话号码？

---

编辑:

我刚刚收到一些很棒的帮助:group_concat 创造奇迹!现在我正在尝试正确格式化查询返回。

目标:将 GROUP_CONCAT 返回的值分离到新字段中

当前的 SQL 代码:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(telephone.Number) as TelephoneNumbers, GROUP_CONCAT(telephone.Type) as Types
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID

GROUP_CONACT 的当前结果:

TelephoneNumbers                 Type
321-000-0000,321-000-0000      Main, Fax
321-001-0000                   Mobile

我要实现的目标:

    Main           Fax            Mobile
321-000-0000   321-000-0000        NULL
    NULL           NULL        321-001-0000

尝试:GROUP_CONCAT 中的 WHERE 语句，抛出错误

GROUP_CONCAT(telephone.Number WHERE GROUP_CONCAT(telephone.Type) = "MAIN") as Main

这有可能实现吗？

---

编辑:

最终代码(感谢 user4829935!):

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(tmain.Number) as Main, GROUP_CONCAT(tmobile.Number) as Mobile, GROUP_CONCAT(tfax.Number) as Fax
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone as tmain ON customertelephone.TelephoneID = tmain.TelephoneID AND tmain.type = 'Main'
LEFT JOIN telephone as tmobile ON customertelephone.TelephoneID = tmobile.TelephoneID AND tmobile.type = 'Mobile'
LEFT JOIN telephone as tfax ON customertelephone.TelephoneID = tfax.TelephoneID AND tfax.type = 'Fax'
GROUP BY customer.CustomerID

Answer 1

试试这个:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(telephone.Number)
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID

您将获得以逗号分隔的电话号码。

编辑:

将不同的数字作为不同的字段，如:

name      street      city        state  zip    ... main_phone   fax
John Doe  123 Main St Springfield CA     99999      123-555-5555 123-555-5556

您需要提前知道可能的电话号码类型，并将它们编码到查询中。这是你想要的吗？

那会是这样的:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(tm.Number) as mainTelephone, GROUP_CONCAT(tf.Number) as fax
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone as tm ON customertelephone.TelephoneID = tm.TelephoneID AND tm.type = 'mainTelephone'
LEFT JOIN telephone as tf ON customertelephone.TelephoneID = tf.TelephoneID AND tf.type = 'fax'
GROUP BY customer.CustomerID

(我是凭空打字，因为我没有您的数据来验证我的查询。可能有打字错误)