php - PostgreSQL:在分页输出中使用 row_number()

Question

我有一个 PHP 脚本显示了按“虚拟货币”排序的玩家列表:

$sth = $db->prepare("
select u.id,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset']));

为了显示玩家在列表中的位置，我使用了一个 PHP 变量 $pos，它在打印他们的名字和更多数据时循环递增。

出于各种原因，我希望在 SQL 语句而不是 PHP 中有那个位置。所以我正在尝试以下操作:

$sth = $db->prepare("
select u.id,
        row_number() + ? as pos,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset'], $_GET['offset']));

但是得到ERROR: window function call requires an OVER clause

我正在尝试添加 over(m.money) 但出现语法错误。

我可能误解了 Window Functions文档。

Answer 1

查看用户注释:http://www.postgresql.org/docs/8.4/interactive/functions-window.html

您需要 Over() 包含与整个查询相同的 order by 子句:

$sth = $db->prepare("
select u.id,
        row_number() OVER (order by m.money desc) + ? as pos,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset'], $_GET['offset']));