SQL语句查询字段内重复内容，并按重复次数加序号

SQL语句如何实现查询字段内重复内容，并按重复次数加序号
如Users表中的UserAddress列实现如下结果：
1号楼1单元0101号 1号楼1单元0101号
1号楼1单元0102号 1号楼1单元0102号-1
1号楼1单元0102号 1号楼1单元0102号-2
1号楼1单元0102号 1号楼1单元0102号-3
1号楼1单元0103号 1号楼1单元0103号-1
1号楼1单元0103号 1号楼1单元0103号-2
运行环境：Access 2003
表结构如下图

SELECT  address & IIF(TheSameTotal > 1, '-' & Seq, '')
FROM    (   SELECT  (SELECT COUNT(*)FROM 表1 tt WHERE tt.address = t.address) AS TheSameTotal,
                    (SELECT     COUNT(*)FROM 表1 tt WHERE tt.ID <= t.ID AND  tt.address = t.address) AS Seq,
                    *
            FROM    表1 AS t) AS ttt;

select address from 
(select concat(a.UserAddress, '-',
@r:= case when @addr=a.UserAddress then @r+1 else 1 end) address,
@addr:=a.UserAddress
from (
select
    UserAddress
from
test202207 
where
    UserAddress in ( select UserAddress from test202207 group by UserAddress having count(*) > 1 )) a ,(SELECT @r:=0 ,@addr:='') b)
 c

我这边只有mysql数据库，你看看兼容不兼容，不兼容改一下就可以了，估计除了我也没人给你回答了

select UserAddress,concat(UserAddress,'-',rn)
from (select UserAddress,@rank:=UserAddress,
case when @rank=UserAddress then (@r:=@r+1) 
     else (@r:=1) end as rn
from (select UserAddress
from 表名
order by UserAddress) a,(SELECT @r:=0 ,@rank:='') b
) c

select UserAddress + '-' + row_number() over (partition by UserAddress order by UserAddress) as UserAddress from user;

必须要用sqlserver吗，窗口函数方便点
需要的字段 后面加 row_number()