postgresql 中的 Rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3959692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rownum in postgresql
提问by danidacar
Is there any way to simulate rownum in postgresql ?
有没有办法在 postgresql 中模拟 rownum ？
回答by baklarz2048
Postgresql > 8.4
PostgreSQL > 8.4
SELECT
row_number() OVER (ORDER BY col1) AS i,
e.col1,
e.col2,
...
FROM ...
回答by Wolfgang Vollmann
I have just tested in Postgres 9.1 a solution which is close to Oracle ROWNUM:
我刚刚在 Postgres 9.1 中测试了一个接近 Oracle ROWNUM 的解决方案：
select row_number() over() as id, t.*
from information_schema.tables t;
回答by Buka
Postgresql have limit.
Postgresql 有限制。
Oracle's code:
甲骨文的代码：
select *
from
tbl
where rownum <= 1000;
same in Postgresql's code:
在 Postgresql 的代码中相同：
select *
from
tbl
limit 1000
回答by Kuberchaun
If you just want a number to come back try this.
如果你只是想要一个数字回来试试这个。
create temp sequence temp_seq;
SELECT inline_v1.ROWNUM,inline_v1.c1
FROM
(
select nextval('temp_seq') as ROWNUM, c1
from sometable
)inline_v1;
You can add a order by to the inline_v1 SQL so your ROWNUM has some sequential meaning to your data.
您可以将 order by 添加到 inline_v1 SQL，以便您的 ROWNUM 对您的数据具有一定的顺序意义。
select nextval('temp_seq') as ROWNUM, c1
from sometable
ORDER BY c1 desc;
Might not be the fastest, but it's an option if you really do need them.
可能不是最快的，但如果您确实需要它们，它是一种选择。
回答by Sachin Shanbhag
Postgresql does not have an equivalent of Oracle's ROWNUM. In many cases you can achieve the same result by using LIMIT and OFFSET in your query.
Postgresql 没有相当于 Oracle 的 ROWNUM。在许多情况下，您可以通过在查询中使用 LIMIT 和 OFFSET 来获得相同的结果。
回答by Lorenzo Zuluaga
use the limit clausule, with the offset to choose the row number -1 so if u wanna get the number 8 row so use:
使用 limit clausule 和偏移量来选择行号 -1 所以如果你想得到第 8 行，那么使用：
limit 1 offset 7
限制 1 偏移 7
回答by Kaushik Nayak
If you have a unique key, you may use COUNT(*) OVER ( ORDER BY unique_key ) as ROWNUM
如果你有一个唯一的密钥，你可以使用 COUNT(*) OVER ( ORDER BY unique_key ) as ROWNUM
SELECT t.*, count(*) OVER (ORDER BY k ) ROWNUM
FROM yourtable t;
| k | n | rownum |
|---|-------|--------|
| a | TEST1 | 1 |
| b | TEST2 | 2 |
| c | TEST2 | 3 |
| d | TEST4 | 4 |
