PostgreSQL：在分页输出中使用 row_number()

发布于 2024-10-10 10:28:32 字数 1175 浏览 2 评论 0原文

我有一个 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']));

但是得到 错误：窗口函数调用需要 OVER 子句

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

我可能误解了窗口函数文档。

原文

I have a PHP script displaying a list of players sorted by their "virtual money":

$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']));

To show a player position in the list I use a PHP variable $pos which is incremented in a loop while printing their names and further data.

I would like to have that position in the SQL statement instead of PHP for various reasons. So I'm trying the following:

$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']));

But get the ERROR: window function call requires an OVER clause

I'm trying to add over(m.money) but get a syntax error.

I'm probably misunderstanding the Window Functions doc.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怪我入戏太深 2024-10-17 10:28:32

检查以下用户注释： 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']));

Check the user notes on: http://www.postgresql.org/docs/8.4/interactive/functions-window.html

You will need the Over() to contain the same order by clause as the whole query:

$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']));

回复收藏 0 原文