SQL查询-如何输出重复值

Question

我有一个非常简单的COUNT查询来显示表中用户有重复记录的位置。

SELECT      [name], COUNT([name])
FROM        [user]
GROUP BY    [name]
HAVING      COUNT([name]) > 1
ORDER BY    COUNT([name])

这将输出类似以下内容：

Name       | Count
---------------------
Mr Smith   | 3
Mr Foster  | 3
Mr Johnson | 4

然后我想将找到的每一行与表中的另一个字段(引用)一起输出，如下所示：

Name       | Reference
---------------------
Mr Smith   | REF-1
Mr Smith   | REF-2
Mr Smith   | REF-3
Mr Foster  | REF-4
Mr Foster  | REF-5
Mr Foster  | REF-6
Mr Johnson | REF-7
Mr Johnson | REF-8
Mr Johnson | REF-9
Mr Johnson | REF-10

做这件事最好的方法是什么？如果这很简单，我很抱歉--我就是想不通！提前谢谢。

Answer 1

您可以使用row_number()

SELECT name, reference,
       CONCAT('Ref-', ROW_NUMBER() OVER (ORDER BY cnt, name) as ref
FROM (SELECT u.*,
             COUNT(*) OVER (PARTITION BY name) as cnt
      FROM [user] u
     ) u
WHERE cnt > 1
ORDER BY cnt, name;