首选MySQL GROUP BY [英] MySQL GROUP BY with preference

问题描述

是否可以使用GROUP BY和记录偏好？

例如，我有大量联系人数据可能包含或不包含所有信息如果可能看起来像这样的CSV感觉：

 测试用户，地址1，地址2，[e-mail] 
， ，，[e-mail] 
  

如果我使用GROUP BY电子邮件，我很乐意提取更有关的记录。

希望有道理？

您的
Chris

解决方案

您可以使用聚合函数为每封电子邮件获取更多相关记录。

我认为这个查询会给你最好的结果：

$ p $ SELECT emailAddress，max（concat（fullName ，'，'，address1，'，'，address2））
FROM table
GROUP BY emailAddress


它将为每个电子邮件地址返回最丰富的行，但所有数据都将在一个字符串中返回（逗号分隔），因此您必须以某种方式解析它。

如果表现没有问题，你想在不同的领域得到一个正常的结果集，那么你可以去一个：

  SELECT table.emailAddress，fullName，address1，address2 
 FROM 
表JOIN 
（SELECT emailAddress，
 max（concat（fullName，address1，address2 ））as bestRowInOneString 
 FROM table 
 GROUP BY emailAddress 
）bestRowsSubQuery 
 ON 
 concat（table.fullname，table.address1，table.address2）= bestRowsSubQuery.bestRowInOneString 
 AND table.emailAddress = bestRowsSubQuery.emailAddress 
  

is it possible to use GROUP BY with a preference for records?

For instance I have whole bunch of contact data that may or may not contain all info - in a CSV sense if might look like this:

Test User, Address1, Address2, [e-mail]  
, , , [e-mail]

If I was to GROUP BY email, I would love to extract the more relevant record.

Hope that makes sense?

Yours, Chris

解决方案

You could use an aggregate function for getting the more 'relevant' record for each email.
I think this query would get you the best result:

SELECT emailAddress, max(concat(fullName,',',address1,',',address2))
FROM table
GROUP BY emailAddress

It will return the richest row for each email address but all data will be returned within one string (comma-separated) so you will have to parse it somehow.
If performance is no issue and you'd like to get a normal result set in separate fields then you could go with one:

SELECT table.emailAddress, fullName, address1, address2 
FROM 
table JOIN 
    (SELECT emailAddress, 
       max(concat(fullName,address1,address2)) as bestRowInOneString
    FROM table
    GROUP BY emailAddress
    ) bestRowsSubQuery 
 ON 
   concat(table.fullname,table.address1,table.address2) = bestRowsSubQuery.bestRowInOneString
   AND table.emailAddress = bestRowsSubQuery.emailAddress

这篇关于首选MySQL GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！