时间戳

首页 > 解决方案 > 如何为具有相同值的结果添加自定义递增计数器？

sql-server - 如何为具有相同值的结果添加自定义递增计数器？

问题描述

我有以下 MySQL 查询，用于从特定位置重命名文件：

SELECT REPLACE('copy "S:' + RIGHT(Path, LEN(Path) - 10) + '.eps" ','/','\'),' "C:\EPS\' + barcode + ' ().eps"'
FROM product 
WHERE barcode IN ('1234','6789);

这给了我以下结果，这很好：

copy "S:\C\image1.eps"  |  "C:\EPS\1234 ().eps"
copy "S:\C\image2.eps"  |  "C:\EPS\1234 ().eps"
copy "S:\C\image3.eps"  |  "C:\EPS\1234 ().eps"
copy "S:\C\image4.eps"  |  "C:\EPS\1234 ().eps"
copy "S:\C\image5.eps"  |  "C:\EPS\6789 ().eps"
copy "S:\C\image6.eps"  |  "C:\EPS\6789 ().eps"

如何为每个匹配值添加自定义计数器以获得以下结果？

copy "S:\C\image1.eps"  |  "C:\EPS\1234 (1).eps"
copy "S:\C\image2.eps"  |  "C:\EPS\1234 (2).eps"
copy "S:\C\image3.eps"  |  "C:\EPS\1234 (3).eps"
copy "S:\C\image4.eps"  |  "C:\EPS\1234 (4).eps"
copy "S:\C\image5.eps"  |  "C:\EPS\6789 (1).eps"
copy "S:\C\image6.eps"  |  "C:\EPS\6789 (2).eps"

标签： sql-servercounterincrement

解决方案

你的代码看起来像 SQL Server，所以使用ROW_NUMBER()窗口函数：

SELECT REPLACE('copy "S:' + RIGHT(Path, LEN(Path) - 10) + '.eps" ','/','\'),
       '"C:\EPS\' + barcode + ' (' + 
       CAST(ROW_NUMBER() OVER (PARTITION BY barcode ORDER BY Path) AS VARCHAR(10))
       +').eps"'
FROM product 
WHERE barcode IN ('1234','6789');

见演示。
结果：

copy "S:\C\image1.eps"  |  "C:\EPS\1234 (1).eps"
copy "S:\C\image2.eps"  |  "C:\EPS\1234 (2).eps"
copy "S:\C\image3.eps"  |  "C:\EPS\1234 (3).eps"
copy "S:\C\image4.eps"  |  "C:\EPS\1234 (4).eps"
copy "S:\C\image5.eps"  |  "C:\EPS\6789 (1).eps"
copy "S:\C\image6.eps"  |  "C:\EPS\6789 (2).eps"

推荐阅读