gpt4 book ai didi

php - SQL Select 对多列进行计数并排序

转载 作者:行者123 更新时间:2023-11-29 18:02:29 25 4
gpt4 key购买 nike

所以我尝试从最高声望和最高经验(xp)进行排序

public function getRank($user, $skill, $mode) {
$skill2 = strtolower($skill)."_xp";
$skill3 = strtolower($skill)."_prestiges";
$stmt = $this->conn->prepare("SELECT (
SELECT COUNT(*) FROM hs_users WHERE mode = :mode AND (
$skill3 >= u.$skill3 AND $skill2 >= u.$skill2
)
) AS rank
FROM hs_users u
WHERE username = :user AND mode = :mode2
LIMIT 1");

$stmt->bindParam(":user", $user);
$stmt->bindParam(":mode", $mode);
$stmt->bindParam(":mode2", $mode);
$stmt->execute();
return $stmt->fetchColumn();
}

目前我得到了重复的排名,有两个人排名第一。

例如如果某人是声望 1 和 3,000,000 经验,然后某人是声望 0 和 4,000,000 经验,他们会获得相同的排名,至于第二个人应该是排名 2。

我一直在尝试 ORDER BY 但它似乎不起作用。我对 SQL 查询不太有经验,如果您有任何指示或可以帮助我,那就太好了。

最佳答案

如果声望是主要排名属性:

$stmt =  $this->conn->prepare("SELECT id, $skill3, $skill2
FROM hs_users
WHERE username = :user
AND mode = :mode
");
$stmt->bindParam(":user",$user);
$stmt->bindParam(":mode",$mode);
if($stmt->execute()){
$user = $stmt->fetch(PDO::FETCH_ASSOC);// get the users id, prestige and experience level
}
$higherPrestiges = array();
$stmt = $this->conn->prepare("SELECT id
FROM hs_users
WHERE $skill3 >= :skill3
AND mode = :mode");
$stmt->bindParam(":skill3",$user[$skill3]);
$stmt->bindParam(":mode",$mode);
if($stmt->execute()){
while($row = $stmt->fetch(PDO::FETCH_COLUMN)){ //get all users that have higher prestige
$higherPrestiges[$row] = $row;
}

}
if(count($higherPrestiges) > 0){
$higherPrestiges_implode = implode(",",$higherPrestiges);
}else{
$higherPrestiges_implode = "0";
}
$lowerExperiences = array();
$stmt = $this->conn->prepare("SELECT id
FROM hs_users
WHERE $skill3 = :skill3
AND $skill2 < :skill2
AND mode = :mode");
$stmt->bindParam(":skill2",$user[$skill2]);
$stmt->bindParam(":skill3",$user[$skill3]);
$stmt->bindParam(":mode",$mode);
if($stmt->execute()){
while($row = $stmt->fetch(PDO::FETCH_COLUMN)){//get users with lower experience with the same prestige
$lowerExperiences[$row] = $row;
}
}

if(count($lowerExperiences) > 0){
$lowerExperiences_implode = implode(",",$lowerExperiences);
}else{
$lowerExperiences_implode = "0";
}

$stmt = $this->conn->prepare("SELECT COUNT(id)
FROM hs_users
WHERE mode = :mode
AND id IN(".$higherPrestiges_implode.")
AND id NOT IN(".$lowerExperiences_implode.")");
$stmt->bindParam(":mode",$mode);
if($stmt->execute()){
$rank = $stmt->fetch(PDO::FETCH_COLUMN);//get number of users that have higher or equal prestige and exclude the ones with lower experience
}

只需将查询中对“id”的引用替换为表中主键的列名称

我确实意识到最后 3 个查询可以与 IN 子句中的子查询组合,但这应该更容易理解

关于php - SQL Select 对多列进行计数并排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48214518/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com