mysql - 按聚合(计数)对查询结果进行排名

Question

我正在尝试通过聚合函数获取排名值 - 我想看看它是否可以在 MySQL 查询中完成(而不是 ORM 调用查询)。

我的表格和数据是这样的:

CREATE TABLE `interactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `interactions` VALUES
(1, "xyz", "2017-01-01 00:05:01"),
(2, "xyz", "2017-01-01 00:05:10"),
(3, "abc", "2017-01-01 00:05:21"),
(4, "xyz", "2017-01-01 00:05:43"),
(5, "def", "2017-01-01 00:05:47"),
(6, "xyz", "2017-01-01 00:05:49"),
(7, "abc", "2017-01-01 00:05:50"),
(8, "abc", "2017-01-01 00:05:59");

到目前为止我有:

set @curRank := 0;

select
    @curRank := @curRank + 1 AS rank,
    der.account,
    der.searches
from
    interactions
right join
    (select account, count(id) AS searches from interactions group by account order by searches) AS der
on
    der.account = interactions.account;

但这会输出每个帐户(具有正确的 searches 值 - 但排名不止一次):

 1    abc        3
 2    abc        3
 3    abc        3
 4    def        1
 5    xyz        4
 6    xyz        4
 7    xyz        4
 8    xyz        4

我在找:

 1    abc        3
 2    def        1
 3    xyz        4

我应该提一下，我不关心联合排名 - 如果两个帐户最终在表中的计数相同，那么它们是否一个接一个地排名(或什么顺序)并不重要。

Answer 1

向 SELECT 添加一个 DISTINCT 修复查询:

set @curRank := 0;

select distinct 
    der.account,
    der.searches,
    @curRank := @curRank + 1 AS rank
from
    interactions
right join
    (select account, count(id) AS searches from interactions group by account order by searches) AS der
on
    der.account = interactions.account;