MySQL查询 "One to Many"问题

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] => jm@mc.com,jm@mc.com,jm@mc.com,jm@mc.com
    [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] => 
)

现在你可以看到结果就像我想要的那样，除了当一个项目有多个有效项目时，即在这种情况下，约翰有 4 种类型的电话号码，这会导致数据库中的其他记录成倍增加因此。所以在这种情况下，您在所有其他支持表中得到 4 个相同的项目。

我已经尽我所能，但也许无法完成，但我想我会再试一次，看看是否有人会看它并说哦，是的，你错过了 5 个字母，可以让它工作，或者某物。在这一点上，即使是“你的愚蠢行不通”也会很棒。

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

更新:

我现在感觉像个菜鸟，我犯了一个典型的错误:我在没有完全验证的情况下检查了我的结果，但同时我提供的信息也不清楚。我将解释我原来的解决方案是有效的，除了当我有 3 个工作(类型 = 1)号码时，我最终会得到像 phone_type => 1 和 phone => 555-1212,555-1213,555-1214 这样的结果，这是很好，但是当我有 2 个工作和 1 个家时，类型标识符是无用的，但我从来没有说过你可以有一个以上的任何类型的联系人所以实际上我下面的两个答案都是正确的，说 sql 是一个有点畸形，但我知道他在说什么，所以它实际上在 Nose 上效果更好，更正确。顺便说一句，只是从类型字段中拉出 distint 也不会这样做......我试过了。

部分新查询方案:

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

问题是，一旦您加入 PHONE(在您的示例中)，查询就会返回四个记录。在顶级 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 有很大不同......