如何在SQL中基于组递增整数

Question

我的问题是：

IF Object_id('tempdb..##delist') IS NOT NULL
  DROP TABLE ##delist
SELECT jl.jobid as 'JobID',
       jl.CustomObjectName as 'DEName',
       cols.name as 'ColumnName',
       null as 'ColumnNumber'
INTO   ##delist
FROM   ##joblist AS jl WITH (nolock)
       JOIN sys.tables tbls with (nolock) on tbls.name = jl.CustomObjectName
       JOIN sys.columns cols with (nolock) on cols.object_id = tbls.object_id
WHERE cols.name <> '_CustomObjectKey'

以下是我的结果集：

我最终要寻找的是一种通过“DEName”递增地对ColumnNumber数据字段进行编号的方法。换句话说，对于每个DEName的每个ColumnName，我希望ColumnNumber从1开始，然后转到每个DEName有多少个唯一的ColumnName。有什么想法吗？

Answer 1

您可以使用以下命令：

select . . .,
       row_number() over (partition by jl.CustomObjectName order by (select NULL)) as columnNumber