MySQL 查询“一对多”问题

Question

好吧，我将尽力解释这一点，也许有人会理解。我正在构建一个 CRM 应用程序，并有以下表格：联系人、电子邮件、电话、网站和地址。我一直在尝试创建一个将所有信息收集到一个结果集中的查询。我已经找到了一种对 99.9% 有效的方法，但我认为我错过了让那 1% 有效的方法，并且为了找到它而自杀了。

我的查询如下所示：

SELECT 
contacts.full_name,
contacts.title,
contacts.company,
contacts.background,
GROUP_CONCAT( email.email_type ORDER BY  email.email_type)as email_type,
GROUP_CONCAT( email.email ORDER BY  email.email_type)as email,
GROUP_CONCAT( phone.phone_type ORDER BY phone.phone_type)as phone_type,
GROUP_CONCAT( phone.phone ORDER BY phone.phone_type)as phone,
GROUP_CONCAT( website.website_type ORDER BY website.website_type)as website_type,
GROUP_CONCAT( website.website ORDER BY website.website_type)as website,
GROUP_CONCAT( address.type ORDER BY  address.type ) as address_type,
GROUP_CONCAT( address.address_street ORDER BY  address.type ) as street,
GROUP_CONCAT( address.address_city ORDER BY  address.type ) as city,
GROUP_CONCAT( address.address_state ORDER BY  address.type ) as state,
GROUP_CONCAT( address.address_zip ORDER BY  address.type ) as zip,
GROUP_CONCAT( address.address_country ORDER BY  address.type) as country
FROM
contacts
Left Join email ON contacts.id = email.contact_id
Left Join phone ON contacts.id = phone.contact_id
Left Join website ON contacts.id = website.contact_id
Left Join address ON contacts.id = address.contact_id
GROUP BY
contacts.id
ORDER BY
contacts.id ASC

现在就像我说的，它的工作方式 99.9% 符合我想要的方式，但这里是结果集： （现在这是一个模拟结果对象，但它遵循查询后当前吐出的内容。）

stdClass Object
(
    [full_name] => John Mueller
    [title] => President
    [company] => Mueller Co.
    [background] => This is the contacts background info.
    [email_type] => 1,1,1,1
    [email] => [email protected],[email protected],[email protected],[email protected]
    [phone_type] => 1,2,3,4
    [phone] => (123) 555-1212,(123) 555-1213,(123) 555-1214,(123) 555-1215
    [website_type] => 1,1,1,1
    [website] => www.mc.com,www.mc.com,www.mc.com,www.mc.com
    [address_type] => 1,1,1,1
    [street] => {address_1},{address_1},{address_1},{address_1}
    [city] => {city_1},{city_1},{city_1},{city_1}
    [state] => {state_1},{state_1},{state_1},{state_1}
    [zip] => {zip_1},{zip_1},{zip_1},{zip_1}
    [country] => 
)

现在，您可以看到结果的行为就像我希望的那样，除了当其中一个项目具有多个有效项目时，即在本例中John 有 4 种类型的电话号码，这会导致数据库中的其他记录相应增加。因此，在这种情况下，您会在所有其他支持表中获得 4 个相同的项目。

我已经尝试了我能做的一切，也许无法完成，但我想我会再试一次，看看是否有人会看它并说哦，是的，你缺少 5 个字母，这将使其工作或其他什么。在这一点上，即使是“你的愚蠢行不通”也很棒。

再次感谢任何人可以提供的任何帮助！

更新：

我现在感觉像个菜鸟，我犯了一个典型的错误：我在没有完全验证的情况下检查了我的结果，但同时根据我提供的信息并不清楚。我将解释我原来的解决方案的工作原理，除了当我有 3 个工作（类型 = 1）号码时，我最终会得到类似的结果：phone_type => 1、电话=> 555-1212,555-1213,555-1214 这很好，但当我有 2 个工作和 1 个家庭时，类型标识符就没用了，但我从未说过您可以为联系人拥有多个任何类型，所以实际上两个我下面的答案是正确的，也就是说sql有点格式错误，但我知道他在说什么，所以它实际上在鼻子上工作得更好并且更正确。顺便说一句，仅仅从类型字段中提取出 disintt 也不会做到这一点......我尝试过。

Patial 新的查询解决方案：

SELECT

contacts.full_name,
contacts.title,
contacts.company,
contacts.background,

inner_phone.phone,
inner_phone_type.phone_type

FROM
contacts

left Join (SELECT phone.contact_id, GROUP_CONCAT(phone.phone ORDER BY phone.phone_type) as phone FROM phone GROUP BY phone.contact_id ) inner_phone ON contacts.id = inner_phone.contact_id
left Join (SELECT phone.contact_id, GROUP_CONCAT(phone.phone_type ORDER BY phone.phone_type) as phone_type FROM phone GROUP BY phone.contact_id ) inner_phone_type ON contacts.id = inner_phone_type.contact_id

ORDER BY
contacts.id ASC

谢谢您的回答，作为旁注，我刚刚取消了价格过高的 Experts Exchange，这更容易使用和找到您正在寻找的内容，而且最重要的是免费;) - 再次感谢。

Answer 1

问题是，一旦您通过电话加入（在您的示例中），查询就会返回四条记录。在顶级 SELECT 列表中使用 GROUP_CONCAT 并不能解决这个问题。我想到的第一个解决方案是使用内部查询来保证您只会返回给定联系人的一条记录。

SELECT 
contacts.full_name,
[...]
inner_phone.phones
FROM contacts 
LEFT JOIN (SELECT GROUP_CONCAT(phone ORDER BY phone_type) AS phones, contact_id FROM phone) inner_phone ON contacts.ID = inner_phone.contact_id
[... etc. for other tables ...]

这应该会让你走上正轨，除非 MySQL 的子查询行为与 MSSQL/Oracle 有很大不同......

Answer 2

把 DISTINCT 扔进去。

GROUP_CONCAT(DISTINCT email.email_type ORDER BY email.email_type)as email_type,

参考

Answer 3

首先将“基本”信息获取到应用程序（不重复的字段），然后使用主表单中的子表单对重复信息进行单独的查询不是更容易吗？

或者，作为折衷方案，创建一个最多显示 N 个重复字段的视图。