因此,我试图在我的市场网站上的面板中显示每个卖家的个人排名。对于这个特定的排名,我计算每个卖家有多少追随者,按降序对列表进行排序,然后获得每个卖家的位置,以便他们知道自己的排名。我正在使用一个有效的查询,除了当两个卖家有相同数量的追随者时,位置每次都会改变。当有大量的卖家拥有相同数量的追随者时,这尤其令人困扰。
查询(PHP格式):
<?php
$query = "SELECT rank
FROM (
SELECT vendor_id, followers, @rank:=@rank+1 AS rank
FROM (
SELECT vendor_id, COUNT(*) AS followers
FROM $table
GROUP BY vendor_id
ORDER BY followers DESC
) as sq,
(SELECT @rank:=0) AS tr
) as q WHERE vendor_id = $vendorId";
?>
其中$vendorId
=当前卖方的ID。
所以,再一次,主要的问题是,如果有10个卖家,其中7个都有1个追随者。每次卖家刷新时,他们都会看到从4到10的排名。我希望它只是说4为所有卖家谁有1个追随者。
更新:下面的查询给了我想要的,尽管我仍然喜欢把工作负载推到PHP上,而不是把它都放在MySQL上。
<?php
$connection->query("SET @curRank:=1, @prevRank:=0, @incRank=1;");
$query = "SELECT rank
FROM (
SELECT vendor_id, followers,
@curRank := IF(@prevRank = followers, @curRank, @incRank) AS rank,
@incRank := @incRank + 1,
@prevRank := followers
FROM (
SELECT vendor_id, COUNT(1) AS followers
FROM $favTable
GROUP BY vendor_id
ORDER BY followers DESC, vendor_id DESC
) as sq,
(SELECT @rank:=0) AS tr
) as q WHERE vendor_id = $vendorId";
$result = $connection->fetchOne($query);
https://stackoverflow.com/questions/51867263
复制相似问题