MySQL:根据另一列值选择列中的唯一值 [英] MySQL: Select unique value in column based on another columns value

问题描述

我的桌子是这样设置的:

I have a table set up like this:

id    |      ip      |   name    
---------------------------------
1     | 54.34.32.222 |   John 
2     | 23.44.64.843 |   Rick 
3     | 54.34.32.222 |   John 
4     | 23.44.64.843 |   John 
5     | 14.432.45.45 |   Lisa 
6     | 54.34.32.222 |   Lisa 
7     | 14.432.45.45 |   Lisa

我只想获取每个名称的唯一 IP.例如，54.34.32.222"对约翰出现了两次，所以我只想抓取第一行.但是Lisa也出现了54.34.32.222"，所以我也想抢那个IP.

I only want to grab a unique IP per name. For example, "54.34.32.222" appears for John twice, so I only want to grab the first row. But "54.34.32.222" also appears for Lisa, so I would like to grab that IP as well.

结果应该是这样的:

id    |      ip      |   name    
---------------------------------
1     | 54.34.32.222 |   John 
2     | 23.44.64.843 |   Rick 
4     | 23.44.64.843 |   John 
5     | 14.432.45.45 |   Lisa 
6     | 54.34.32.222 |   Lisa 

你如何计算名字出现的次数?这样做时，它会计算 ip 在名称中出现的次数，但我想要相反的.

How would you count the amount of times names appear? When doing so, it counts how many times the ip appears within the name, but I want the opposite.

SELECT MIN(id), COUNT(name), ip, name FROM yourTable GROUP BY ip, name

推荐答案

你从来没有提到过在ip-name-name 对.但是，根据您的示例输出，您似乎保留了具有最小 id 值的记录.在这种情况下，我们可以在分组时只取 MIN(id) 以获得所需的结果:

You never mentioned how you want to determine which record to retain in the case of duplicate ip-name pairs. However, based on your sample output it appears you are retaining the record with the smallest id value. In this case, we can just take the MIN(id) while grouping to get the desired result:

SELECT MIN(id), ip, name
FROM yourTable
GROUP BY ip, name

点击以下链接查看正在运行的演示:

SQLFiddle

这篇关于MySQL:根据另一列值选择列中的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！