在选择中生成序列号 [英] Generate sequence number in select

问题描述

我们如何生成一个以1001开头的序列号，并在select语句中使用lpad将该序列值添加为"0".这用于将记录从Db表插入CSV文件中，并将此代码写在cursor中.谁能建议最好的方法.我得到了一个示例查询，该查询有效吗?

How can we generate a sequence number which starts with 1001 and use in the select statement and lpad the sequence value with '0's .This for inserting records into CSV file from Db tables and this code is written in cursor . Can anyone suggest the best way to do this .I got an example query, will the query work ?

select lpad((create sequence customer.seq as int start with 1001 increment by 1),11,0);

推荐答案

在Oracle中执行此操作的最佳方法是使用rownum或row_number():

The best way to do this in Oracle is to use rownum or row_number():

select lpad(1000 + rownum, 11, 0) as newcol, . . .
from t;

在游标中也不需要序列或变量.

There is no need for a sequence or for a variable in the cursor either.

您可能还会考虑使用游标的替代方法.

You might consider alternatives to using a cursor as well.

这篇关于在选择中生成序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！