时间戳

首页 > 解决方案 > 相同的查询使用 row_number() 选择不同的数据

sql - 相同的查询使用 row_number() 选择不同的数据

问题描述

假设我有两张桌子。第一个表代表帐户，第二个表代表字符。表与“AccountId”列连接。根据o游戏，角色与帐户相关联，每个帐户可以包含4个角色。

在我的网站中，我有两个页面。第一个页面称为“Characterlist.php”，它生成游戏中的所有角色，具有一个特定的权限（帐户表中的列），该权限等于 0。

我正在使用这个查询：

$query = "WITH 
        Acc AS 
          (SELECT AccountId, Authority 
           FROM Account 
           WHERE Authority = '0'),
        Char AS 
          (SELECT Character.* 
          FROM Character, Acc 
          WHERE Character.AccountId = Acc.AccountId),
        Res AS 
         (SELECT *, 
         ROW_NUMBER() OVER 
        (ORDER BY Reput DESC) AS row_number 
         FROM Char)
    SELECT Res.Class, 
           Res.Name, 
           Res.Level, 
           Res.HeroLevel, 
           Res.Reput, 
           Res.row_number 
      FROM Res 
      WHERE Res.row_number >= '$charlistpagemin' 
      AND Res.row_number <= '$charlistpagemax'";

$charlistpagemin 和 $vharlistpagemax 是我用来将整个字符列表分成几页的 php 变量。

当我搜索我的测试帐户的字符时，他们的位置根据他们的 Reputation 生成并由 row_number() 排序是好的。

然后我有第二页 userpanel.php，它只显示给签名用户，他们可以在另一个列表中看到他们的游戏角色。

我使用几乎相同的查询，只是最后有不同的规则。

$query = 
"WITH 
 Acc AS 
    (SELECT AccountId, 
     Authority 
     FROM Account 
     WHERE Authority = '0'),
  Char AS 
     (SELECT Character.* 
      FROM Character, Acc 
      WHERE Character.AccountId = Acc.AccountId),
  Res AS 
     (SELECT *, 
      ROW_NUMBER() 
      OVER (ORDER BY Reput DESC) AS row_number FROM Char)
SELECT Res.AccountId, 
       Res.Class, 
       Res.Name, 
       Res.Level, 
       Res.HeroLevel, 
       Res.Reput, 
       Res.row_number 
  FROM Res 
  WHERE Res.AccountId = '" . $_SESSION["accountid"] . "'";

还有问题。根据 Reputation，它们的位置与 characterlist.php 中的位置不同（差）。哪里有问题？

编辑：表“帐户”看起来像：

AccountId | Authority | ... |

表“字符”如下所示：

AccountId | Class | Name | Level | HeroLevel | Reput | ... |

我的测试账户有

AccountId: xxx | Authority: 0 | ... |

并且有字符

AccountId: XXX | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |

characterlist.php 中显示的预期结果是

Position: 139 | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 140 | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 141 | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 142 | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 |

但是 userpanel.php 中的坏结果是：

Position: 110 | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 111 | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 112 | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 113 | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 |

简单地说，我的问题在于用户面板中的位置发生了变化。真实位置显示在字符列表中。要根据 Reputation 表中的排序数据计算位置，我使用 row_number() 函数，如我的查询中所示。

标签： sqlsql-serverdatabase

解决方案

Your data has ties for reput and your row_number() expression is:

ROW_NUMBER() OVER (ORDER BY Reput DESC) AS row_number

Ordering in SQL is unstable. That means that rows with ties are in an arbitrary and indeterminate order -- and this order might change from one execution to the next.

Why is sorting unstable? Easy. SQL tables represent unordered sets, so there is no "underlying" ordering to define what happens in the case of ties.

The simple solution is to add another key. In your case, I think Name might be sufficient:

ROW_NUMBER() OVER (ORDER BY Reput DESC, Name) AS row_number

推荐阅读