php - 获取 $rank 变量并在表中更新它

Question

我在获取变量的排名值并将其更新为列时遇到问题。基本上我的意思是，下面我有一个代码，从表“银行”中选择列“银行账户”，我根据银行余额对银行账户进行排名。我如何才能根据每个帐户获得的评级来选择更新“bankaccountranking”列。

示例:排名第一的银行账户是 John Smith。我将如何编写代码以便更新 John Smith 在表“bank”中的“bankaccount ranking”列？我尝试使用 $rank 变量进行更新，但这不起作用。

下面是代码:(提前致谢)

$sessionid = $_SESSION['uid'];
$info = $conn->prepare("SELECT `bankaccount`,`bankbalance` FROM `bank` WHERE           id:id      ORDER BY `bankbalance` DESC");
$info ->bindParam(':id', $sessionid , PDO::PARAM_INT);
$info->execute();


 $rank = 0;


while ($userinfo = $info->fetchobject()) {
$rank++;
echo "<b>$rank</b> . $userinfo->bankaccount &nbsp&nbsp&nbsp <b>Bank                    Balance:</b>           $userinfo->bankbalance </br>"; 

Answer 1

与其不断地用多个查询访问数据库，不如考虑像这样一次完成

UPDATE bank t JOIN 
(
  SELECT id, bankaccount, 
  (
    SELECT COUNT(*)
      FROM bank
     WHERE id = b.id
       AND bankbalance > b.bankbalance
  ) + 1 rank
    FROM bank b
   WHERE id = 1
) s 
   ON t.id = s.id
  AND t.bankaccount = s.bankaccount
  SET t.bankaccountranking = rank;

这是 SQLFiddle 演示

或者使用两个语句，利用用户变量和 UPDATE 中的 ORDER BY

SET @rnum = 0;
UPDATE bank
   SET bankaccountranking = (@rnum := @rnum + 1)
 WHERE id = 1
 ORDER BY bankbalance DESC;

这是 SQLFiddle 演示

---

现在的 php 代码可能是这样的

$sessionid = $_SESSION['uid'];

$sql = "UPDATE bank t JOIN 
(
  SELECT id, bankaccount, 
  (
    SELECT COUNT(*)
      FROM bank
     WHERE id = b.id
       AND bankbalance > b.bankbalance
  ) + 1 rank
    FROM bank b
   WHERE id = :id
) s 
   ON t.id = s.id
  AND t.bankaccount = s.bankaccount
  SET t.bankaccountranking = rank;";

$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $sessionid , PDO::PARAM_INT);
$stmt->execute();

---

更新:用您可以执行的子查询实现等价于 DENSE_RANK() 的分析函数

UPDATE bank t JOIN 
(
  SELECT id, bankaccount, 
  (
    SELECT COUNT(DISTINCT bankbalance)
      FROM bank
     WHERE id = b.id
       AND bankbalance > b.bankbalance
  ) + 1 rank
    FROM bank b
   WHERE id = 1
) s 
   ON t.id = s.id
  AND t.bankaccount = s.bankaccount
  SET t.bankaccountranking = rank;

这是 SQLFiddle 演示

或使用用户( session )变量

SET @r = 0, @b = NULL; 
UPDATE bank b JOIN
(
  SELECT id, bankaccount, @r := IF(@b = bankbalance, @r, @r + 1) rank, @b := bankbalance
    FROM bank
   WHERE id = 1
   ORDER BY bankbalance DESC
) s
    ON b.id = s.id
   AND b.bankaccount = s.bankaccount
   SET bankaccountranking = rank;

这是 SQLFiddle 演示