MySQL - 唯一的电子邮件，具有最多货币的用户 [英] MySQL - Unique email, with user that has most currency

问题描述

说我有一个这样的数据表：

  + ------------ ------------------ + ------------ + --------- + 
 |电子邮件| uname |学分| 
 + ------------------------------ + ------------ +  - -------- + 
 | [e-mail] | barbra | 6 | 
 | [e-mail] |史密斯25 | 
 | [e-mail] |史密斯30 | 
 | [e-mail] |另一个| 25 | 
 | [e-mail] | rob | 8 | 
 | [e-mail] |本| 62 | 
 | [e-mail] | jeb | 2 | 
 | [e-mail] |乔| 5 | 
 + ------------------------------ + ------------ +  - -------- + 
  

所以有重复的电子邮件，你想发送通讯出来但是您不想将多个电子邮件发送到同一个电子邮件地址，并且您希望在电子邮件中拥有用户名。您可能需要通过具有更多信用额的用户名来解决电子邮件中的用户，因为该帐户更有可能处于活动状态。所以你想要一个返回这样的数据的查询：

  + ------------ ------------------ + ------------ + --------- + 
 |电子邮件| uname |学分| 
 + ------------------------------ + ------------ +  - -------- + 
 | [e-mail] | barbra | 6 | 
 | [e-mail] |史密斯30 | 
 | [e-mail] | rob | 8 | 
 | [e-mail] |本| 62 | 
 | [e-mail] |乔| 5 | 
 + ------------------------------ + ------------ +  - -------- + 
  

所以选择在他们的帐户上有更多信用的用户名。

如何编写查询来执行此操作？我看到的唯一的例子是那些不在乎选择哪个用户名的例子，所以你可以让用户获得更少的积分。

解决方案

你可以试试吗？

 选择tab.email，tab.uname 
从（
选择电子邮件，最大（学分）作为学分
从选项卡
通过电子邮件分组
）x.email = tab.email和tab.credits = x.credits $ b $上的x加入选项卡b  

Say I have a table with data like this:

+------------------------------+------------+---------+
| email                        | uname      | credits |
+------------------------------+------------+---------+
| [e-mail]              | barbra     |       6 |
| [e-mail]                | smith      |      25 |
| [e-mail]                | smithy     |      30 |
| [e-mail]              | another    |      25 |
| [e-mail]              | rob        |       8 |
| [e-mail]              | ben        |      62 |
| [e-mail]                | jeb        |       2 |
| [e-mail]                | joe        |       5 |
+------------------------------+------------+---------+

So there is duplicate emails, and you want to send a newsletter out. But you don't want to send multiple of the email to the same email address, and you want to have their username in the email. You probably need to address the user in the email by the username which has more credits, as that account is more likely to be active. So you want a query that'll return data like this:

+------------------------------+------------+---------+
| email                        | uname      | credits |
+------------------------------+------------+---------+
| [e-mail]              | barbra     |       6 |
| [e-mail]                | smithy     |      30 |
| [e-mail]              | rob        |       8 |
| [e-mail]              | ben        |      62 |
| [e-mail]                | joe        |       5 |
+------------------------------+------------+---------+

So it picks the username that has more credits on their account.

How can I write a query to do this? The only examples I've seen are ones that don't care which username it picks, so you could get the user with less credits.

解决方案

Could you try this?

Select tab.email, tab.uname
From(
    Select email, max(credits) as credits
    From tab
    Group by  email
) x join tab on x.email = tab.email and tab.credits = x.credits

这篇关于MySQL - 唯一的电子邮件，具有最多货币的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！