SQL：通过在扩展名前添加 1,2,3... 重命名重复的文件名

Question

我试图弄清楚如何重命名存储文件名的列的内容。目前，该字段中有重复项，我试图通过在每个重复项后添加增量整数来重命名，例如，

ID       | FILENAME
----------------------
1        | file1.ext
2        | file2.ext
3        | file1.ext
4        | file1.ext
5        | file3.ext
6        | file3.ext
7        | file4.ext

因此在上面的示例中，我希望以下内容是唯一的：

ID       | FILENAME
----------------------
1        | file1.ext
3        | file1.ext
4        | file1.ext
5        | file3.ext
6        | file3.ext

通过将文件名更改为：

ID       | FILENAME
----------------------
1        | file1-1.ext
3        | file1-2.ext
4        | file1-3.ext
5        | file3-1.ext
6        | file3-2.ext

我知道如何找到重复，但我不确定如何在扩展之前添加增量或如何首先增加计数。

任何帮助将不胜感激。

Answer 1

使用窗口函数 row_number() 获取数字并替换 字符串操纵。
您没有透露您的 RDBMS。以下查询在 PostgreSQL 9.0 上测试。 MySQL 不支持窗口函数，但大多数其他大型 RDBMS 都支持。

重命名所有文件名：

SELECT id
      ,replace(filename, '.',  
               '-'
               || row_number() OVER (PARTITION BY filename ORDER BY id)
               || '.')
FROM   mytbl

仅重命名重复文件名：

SELECT id
      ,CASE WHEN (count(*) OVER (PARTITION BY filename)) > 1 THEN
          replace(filename, '.',  
                  '-'
                  || row_number() OVER (PARTITION BY filename ORDER BY id)
                  || '.')
       ELSE filename END AS filename
FROM   mytbl;

使用额外请求的功能进行编辑

此版本适用于名称中的多个点或无点。在 PostgreSQL 9.0 中测试。

SELECT id
      ,CASE WHEN (count(*) OVER (PARTITION BY filename)) > 1 THEN
          regexp_replace(filename
              -- pick the longest string from the start not 
             ,'^([^.]*)'containing a '.'
              -- and replace it with itself + row_number
             ,E'\\1-' || row_number() OVER (PARTITION BY filename ORDER BY id))
       ELSE filename END AS filename
FROM   mytbl