SQL 重复查询

Question

表格如下:

id  name1   tom2   bob3   tom4   tom5   harry

I'm looking for a query that would result in the following:

id    name    duplicateposition1     tom     12     bob     13     tom     24     tom     35     harry   1

So duplicateposition for the first tom is 1, for the second tom is 2, and for the third tom is 3. The first bob is 1 and the first harry is 1. Is there an easy query to do this?

Here is my attempt:

SELECT id, name, count(id) from table

我对 sql 有点陌生，所以这是我最好的尝试。

Answer 1

您可以执行以下操作。它按名称对数据进行分区并在分区内分配一个编号。忘了提到我在 SQL Server 2012 中这样做了。

select *, 
  row_number() over (partition by name order by id) as DuplicatePosition
from test
order by id

SQL Fiddle Demo