如何为重复记录的实例编号？只读数据库下生成CSV的方案

我有部分联系人的同一手机号被多次记录，需要删除重复记录中除一条之外的所有条目。但我只有数据库只读权限，所以得生成CSV文件，通过程序接口完成删除操作。

我需要在CSV里给重复记录的实例编号，示例如下：

我已经会统计重复记录数量，但现在需要给每个重复实例编号。当前使用的SQL代码如下：

SELECT DISTINCT
  PS.PHONENUMBER.PHONENUMBER,
  PA1.PERSONPHONENUMBERASSOCID,
  PA1.PERSONID,
  PA1.ISPREFERRED,
  PA1.PHONENUMBERPRIORITYORDER
FROM
  PS.PHONENUMBER
  INNER JOIN PS.PERSONPHONENUMBERASSOC PA1 ON PA1.PHONENUMBERID = PS.PHONENUMBER.PHONENUMBERID
  INNER JOIN PS.PERSONPHONENUMBERASSOC PA2 ON PA1.PERSONID = PA2.PERSONID AND PA1.PHONENUMBERID = PA2.PHONENUMBERID AND PA1.PERSONPHONENUMBERASSOCID <> PA2.PERSONPHONENUMBERASSOCID
ORDER BY
  PA1.PERSONID,
  PA1.PHONENUMBERPRIORITYORDER

要实现给重复手机号的实例编号，用窗口函数ROW_NUMBER()就能轻松完成——按手机号分组，再根据你需要的排序规则给每组内的记录生成连续编号。修改后的SQL如下：

SELECT
  PA1.PERSONPHONENUMBERASSOCID AS id,
  PN.PHONENUMBER,
  ROW_NUMBER() OVER (PARTITION BY PN.PHONENUMBER ORDER BY PA1.PHONENUMBERPRIORITYORDER, PA1.PERSONPHONENUMBERASSOCID) AS Instance,
  PA1.PERSONID,
  PA1.ISPREFERRED,
  PA1.PHONENUMBERPRIORITYORDER
FROM
  PS.PHONENUMBER PN
INNER JOIN PS.PERSONPHONENUMBERASSOC PA1 
  ON PA1.PHONENUMBERID = PN.PHONENUMBERID
-- 若只需要筛选出有重复的记录，保留以下JOIN；若要包含所有手机号记录，直接删除这段JOIN
INNER JOIN PS.PERSONPHONENUMBERASSOC PA2 
  ON PA1.PERSONID = PA2.PERSONID 
  AND PA1.PHONENUMBERID = PA2.PHONENUMBERID 
  AND PA1.PERSONPHONENUMBERASSOCID <> PA2.PERSONPHONENUMBERASSOCID
ORDER BY
  PN.PHONENUMBER,
  Instance;

关键说明： #

• ROW_NUMBER() OVER (PARTITION BY PN.PHONENUMBER ORDER BY ...)：核心逻辑，按手机号分组，每组内优先按PHONENUMBERPRIORITYORDER排序，再按记录ID排序，生成从1开始的连续编号（即示例中的Instance）。
• 如果你需要保留所有手机号记录（包括无重复的），直接删除和PA2的关联语句即可，此时唯一手机号的Instance值会是1。
• 结果中的id字段对应示例里的id，后续可以通过接口筛选Instance > 1的记录进行删除。

内容的提问来源于stack exchange，提问作者Scott Hansen