SQL 在创建/插入时添加序列号 - Teradata
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/772246/
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
Add a sequential number on create / insert - Teradata
提问by AFHood
In oracle we would use rownum on the select as we created this table. Now in teradata, I can't seem to get it to work. There isn't a column that I can sort on and have unique values (lots of duplication) unless I use 3 columns together.
在 oracle 中，当我们创建这个表时，我们将在 select 上使用 rownum。现在在teradata中，我似乎无法让它工作。除非我将 3 列一起使用，否则我没有可以排序并具有唯一值（大量重复）的列。
The old way would be something like,
旧的方式是这样的，
create table temp1 as
select
rownum as insert_num,
col1,
col2,
col3
from tables a join b on a.id=b.id
;
回答by Carlos A. Ibarra
This is how you can do it:
你可以这样做：
create table temp1 as
(
select
sum(1) over( rows unbounded preceding ) insert_num
,col1
,col2
,col3
from a join b on a.id=b.id
) with data ;
回答by Rob Paller
Teradata has a concept of identity columns on their tables beginning around V2R6.x. These columns differ from Oracle's sequence concept in that the number assigned is not guaranteed to be sequential. The identity column in Teradata is simply used to guaranteed row-uniqueness.
从 V2R6.x 开始，Teradata 在他们的表上有一个标识列的概念。这些列与 Oracle 的序列概念的不同之处在于分配的数字不保证是连续的。Teradata 中的标识列仅用于保证行唯一性。
Example:
例子：
CREATE MULTISET TABLE MyTable
(
ColA INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 20)
ColB VARCHAR(20) NOT NULL
)
UNIQUE PRIMARY INDEX pidx (ColA);
Granted, ColA may not be the best primary index for data access or joins with other tables in the data model. It just shows that you coulduse it as the PI on the table.
当然，ColA 可能不是数据访问或与数据模型中其他表连接的最佳主索引。它只是表明您可以将其用作桌面上的 PI。
回答by Ted Elliott
This works too:
这也有效：
create table temp1 as
(
select
ROW_NUMBER() over( ORDER BY col1 ) insert_num
,col1
,col2
,col3
from a join b on a.id=b.id
) with data ;
