database 在 informix 中查询的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/119278/
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
Row numbers for a query in informix
提问by hometoast
I am using informix database, I want a query which you could also generate a row number along with the query
我正在使用 informix 数据库，我想要一个查询，您还可以在查询时生成一个行号
Like
喜欢
select row_number(),firstName,lastName
from students;
row_number() firstName lastName
1 john mathew
2 ricky pointing
3 sachin tendulkar
Here firstName, lastName are from Database, where as row number is generated in a query.
这里 firstName, lastName 来自数据库，其中行号是在查询中生成的。
回答by hometoast
The best way is to use a (newly initialized) sequence.
最好的方法是使用（新初始化的）序列。
begin work;
create sequence myseq;
select myseq.nextval,s.firstName,s.lastName from students s;
drop sequence myseq;
commit work;
回答by hometoast
Given a table called Table3 with 3 columns:
给定一个名为 Table3 的表，有 3 列：
colnum name datatype
======= ===== ===
1 no text;
2 seq number;
3 nm text;
NOTE: seq is a field within the Table that has unique values in ascending order. The numbers do not have to be contiguous.
注意：seq 是表中的一个字段，按升序具有唯一值。数字不必是连续的。
Here is query to return a rownumber (RowNum) along with query result
这是返回行号（RowNum）以及查询结果的查询
SELECT table3.no, table3.seq, Table3.nm,
(SELECT COUNT(*) FROM Table3 AS Temp
WHERE Temp.seq < Table3.seq) + 1 AS RowNum
FROM Table3;
回答by RET
You may not be able to use ROWID in a table that's fragmented across multiple DBSpaces, so any solution that uses ROWID is not particularly portable. It's also strongly discouraged.
您可能无法在跨多个 DBSpace 碎片化的表中使用 ROWID，因此任何使用 ROWID 的解决方案都不是特别具有可移植性。这也是强烈不鼓励的。
If you don't have a SERIAL column in your source table (which is a better way of implementing this as a general concept), have a look at CREATE SEQUENCE, which is more or less the equivalent of an Orrible function that generates unique numbers when SELECTed from (as opposed to SERIAL, which generates the unique number when the row is INSERTed).
如果源表中没有 SERIAL 列（这是将其作为一般概念实现的更好方法），请查看 CREATE SEQUENCE，它或多或少相当于生成唯一数字的 Orrible 函数当 SELECTed from 时（与 SERIAL 相对，后者在行被插入时生成唯一编号）。
回答by Mike Twc
select sum(1) over (order by rowid) as row_number, M.* from systables M
select sum(1) over (order by rowid) as row_number, M.* from systables M
回答by Ilya Kochetov
I think the easiest way would be to use the following code and adjust its return accordingly. SELECT rowid, * FROM table
我认为最简单的方法是使用以下代码并相应地调整其回报。SELECT rowid, * FROM table
It works for me but please note that it will return the row number in the database, not the row number in the query.
它对我有用，但请注意，它将返回数据库中的行号，而不是查询中的行号。
P.S. it's an accepted answer from Experts Exchange.
PS 这是Experts Exchange的公认答案。
回答by Jhollman
I know its an old question, but since i just faced this problem and got a soultion not mentioned here, i tough i could share it, so here it is:
我知道这是一个老问题，但是由于我刚刚遇到了这个问题并且在这里没有提到一个灵魂，我很难分享它，所以这里是：
1- You need to create a FUNCTION that return numbers in a given range:
1- 您需要创建一个返回给定范围内数字的函数：
CREATE FUNCTION fnc_numbers_in_range (pMinNumber INT, pMaxNumber INT)
RETURNING INT as NUMERO;
DEFINE numero INT;
LET numero = 0;
FOR numero = pMinNumber TO pMaxNumber
RETURN numero WITH RESUME;
END FOR;
END FUNCTION;
2- You Cross the results of this Function with the table you want:
2- 您将此函数的结果与您想要的表交叉：
SELECT * FROM TABLE (fnc_numbers_in_range(0,10000)), my_table;
The only thing is that you must know before-hand the number of rows you want, you may get this with the COUNT(*) Function.
唯一的事情是你必须事先知道你想要的行数，你可以用 COUNT(*) 函数得到这个。
This works with my Informix Database, other implementations may need some tweaking.
这适用于我的 Informix 数据库，其他实现可能需要一些调整。
