MYSQL - 按昵称分组但显示所有不同版本？

Question

嗨，小伙子想知道这是否可能，

目前我有一张 table :

"(consensus  CHAR(40) NOT NULL," 
+ "identityb32  CHAR(40) NOT NULL,"
+ "pubdate  CHAR(40) NOT NULL,"
+ "dirport  CHAR(6) NOT NULL,"
+ "ip  CHAR(40) NOT NULL,"
+ "orport  CHAR(40) NOT NULL,"
+ "fingerprint  CHAR(40) NOT NULL,"
+ "nick  CHAR(40) NOT NULL,"
+ "version  CHAR(40) NOT NULL,"
+ "flags  VARCHAR(500) NOT NULL,"
+ "identity  VARCHAR(256) NOT NULL,"
+ "digest  CHAR(40) NOT NULL,"
+ "pubtime  CHAR(40) NOT NULL)"
)

我在这个数据库中插入了很多数据，其中大部分是重复的，但是我想做的是搜索表，并且只输出昵称具有多个指纹的结果

目前我正在使用代码:

SELECT nick, fingerprint, identity, ip, consensus FROM agorahooawayyfoe group by fingerprint;

要做到这一点，但这会向我显示所有按身份分组的昵称，我想要的是将其范围缩小到仅向我显示昵称具有超过 1 个身份的结果，并按这些对我进行分组

这在 mysql 命令中可能吗？

Answer 1

如果您想查看具有多个指纹的昵称，请使用聚合并具有:

select nick
from agorahooawayyfoe
group by nick
having count(distinct fingerprint) > 1;

如果您需要详细信息，请将其加入回表中:

select a.*
from agorahooawayyfoe a join
     (select nick
      from agorahooawayyfoe
      group by nick
      having count(distinct fingerprint) > 1
     ) n
     on a.nick = n.nick;