当前位置: 动力学知识库 > 问答 > 编程问答 >

MySQL Householding: Group Duplicate Addresses And Update Names to Include Both People

问题描述:

If we assume that I already have a query that presented me with the data I want in a very simple way for a mailer: Name, Address, City, State, Zip. But if the file's around 20k, there are a decent amount of people who have the exact same address.

Obviously it's simple to do a GROUP BY Address to remove any duplicates. But the problem I'm running into is not being able to update the names to include both people for a mailer.

If this is two rows in the original data:

Name Address City State Zip

Jerry Seinfeld 129 West 81st Street New York NY 10024

Elaine Benes 129 West 81st Street New York NY 10024

I would like the query output to result in one row where the name looks like this:

Name Address City State Zip

Jerry Seinfeld & Elaine Benes 129 West 81st Street New York NY 10024

And if we want to get fancier, it would be great if they also had the same last name to include something for that in the script so that if both of their last names were "Seinfeld," the output would be:

Name Address City State Zip

Jerry & Elaine Seinfeld 129 West 81st Street New York NY 10024

So generally, I'm just looking for some SELECT *-type query that households these addresses but also includes something to update the names. Thanks!

网友答案:

Below Query would work well, Self tested:

SELECT 
    IF(COUNT(T1.Address) = (SELECT COUNT(DISTINCT T2.Last) FROM t1 T2 WHERE T2.Address = T1.Address), GROUP_CONCAT(T1.First," ",T1.Last SEPARATOR ' & '), CONCAT(GROUP_CONCAT(T1.First SEPARATOR ' & '), ' ',T1.Last)) Name
FROM t1 T1
GROUP BY T1.Address
网友答案:

GROUP_CONCAT()

Examples here: https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

分享给朋友:
您可能感兴趣的文章:
随机阅读: