在创建/插入时添加序列号 - Teradata

发布于 2024-07-17 08:51:54 字数 282 浏览 7 评论 0原文

在 Oracle 中，我们在创建此表时会在选择上使用 rownum。现在在 Teradata 中，我似乎无法让它工作。除非我同时使用 3 列，否则没有一列可以进行排序并且具有唯一值（大量重复）。

旧的方式会是这样的，

create table temp1 as 
  select
    rownum as insert_num,
    col1,
    col2,
    col3
  from tables a join b on a.id=b.id
;

原文

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.

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
;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

还给你自由 2024-07-24 08:51:54

您可以这样做：

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 ;

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 ;

回复收藏 0 原文

拿命拼未来 2024-07-24 08:51:54

Teradata 从 V2R6.x 开始在其表上有标识列的概念。这些列与 Oracle 的序列概念不同，因为分配的数字不保证是连续的。 Teradata 中的标识列仅用于保证行的唯一性。

示例：

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);

当然，ColA 可能不是数据访问或与数据模型中其他表连接的最佳主索引。它只是表明您可以使用它作为桌面上的 PI。

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.

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 could use it as the PI on the table.

回复收藏 0 原文

别在捏我脸啦 2024-07-24 08:51:54

这也有效：

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 ;

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 ;

回复收藏 0 原文

~没有更多了~