Postgres。在一个结果集中查找特定行的位置？

本文是小编为大家收集整理的关于Postgres。在一个结果集中查找特定行的位置？的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我试图弄清楚如何获取查询中单个项目相对于从查询返回的所有项目的相对位置.

例如，得到答案的常用方法是:

single_item = SELECT * FROM table WHERE id=65
result = SELECT * FROM table WHERE published_date < date_value
x=1
foreach(result as item):
    if(item.id == single_item.id):
        required_value = x
    endif
    x++
endforeach

有没有一种简单的方法可以通过单个 postgres 查询获取 required_value?

推荐答案

使用分析/排名/窗口功能 - 8.4 文档链接:

WITH summary AS (
   SELECT t.*,
          ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
     FROM TABLE t)
SELECT s.*
  FROM summary s
 WHERE s.id = 65

不使用 WITH 语法的替代:

SELECT s.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
          FROM TABLE t) s
 WHERE s.id = 65

position 列将是一个整数值，表示 id 值为 65 的记录的位置，基于 published_date 列升序排列.如果希望在有平局时重复位置值，请将 ROW_NUMBER() 替换为 RANK()

本文地址：https://www.itbaoku.cn/post/597712.html

百度翻译此文有道翻译此文

问题描述

I'm trying to figure out how to get the relative position of a single item in a query relative to all the items returned from the query.

For example,the long hand way of getting the answer would be:

single_item = SELECT * FROM table WHERE id=65
result = SELECT * FROM table WHERE published_date < date_value
x=1
foreach(result as item):
    if(item.id == single_item.id):
        required_value = x
    endif
    x++
endforeach

Is there a simple way of getting required_value just through a single postgres query?

WITH summary AS (
   SELECT t.*,
          ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
     FROM TABLE t)
SELECT s.*
  FROM summary s
 WHERE s.id = 65

Alternate without the WITH syntax:

SELECT s.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
          FROM TABLE t) s
 WHERE s.id = 65

The position column will be an integer value representing the location of the record where the id value is 65, based on the published_date column in ascending order. If you want the position value to be duplicated when there are ties, replace ROW_NUMBER() with RANK()

上一篇：排除甲骨文挂起过程的故障

下一篇：Python和MySQL：是否有MySQLdb的替代品？