用于多列分页的SQL查询；了解OR运算子 [英] SQL query for pagination with multiple columns; understand OR operator

问题描述

我正在使用一个工具，该工具会在下面生成Postgresql查询:

I'm using a tool that generates the Postgresql query below:

SELECT
    "id",
    "score"
FROM
    "players"
WHERE
    "score" > '11266' OR ( "score" = '11266' AND "id" > '4482' )
ORDER BY
    "score" ASC,
    "id" ASC
    LIMIT 3

我需要了解为什么OR运算符?

I need to understand why the OR operator?

我的 players 表可以包含许多行，这些行具有相同的score但不相同的id.

My players table can have many rows with the same score but not the same id.

当多行具有相同的score值时是否需要OR?

Is that OR needed when multiple rows has the same score value?

推荐答案

OR的目的(如您所怀疑的)是处理分数中有联系的情况.想法是通过包含id来进行稳定的排序，因此这可以在之后 (score, id)中获得所有内容.

The purpose of the OR -- as you suspect -- is to handle the case where there are ties in the scores. The idea is to make a stable sort by including the id, so this this getting everything after (score, id).

大概，用于score和id的值是最后看到的值(可能在上一页，但这是推测).

Presumably, the values used for score and id are the last values seen (probably on the previous page, but that is speculation).

稳定"排序是一种每次应用时都以相同顺序返回行的排序.因为SQL表表示无序集，所以联系意味着不稳定排序.包含id使其稳定(假设id是唯一的.

A "stable" sort is one that returns the rows in the same order each time it is applied. Because SQL tables represent unordered sets, ties imply an unstable sort. Including the id makes it stable (assuming that id is unique.

Postgres实际上支持简单语法:

Postgres actually supports a simper syntax:

where (score, id) > (11266, 4482)

请注意，我也删除了单引号.这些值看起来像数字，因此应将它们视为数字而不是字符串.

Note that I also removed the single quotes. The values look like numbers so they should be treated as numbers not strings.

这篇关于用于多列分页的SQL查询；了解OR运算子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！