例如：我們有 3*2 重復行如下：
name identity gender
Mary student female
Mary student female
Mary student female
Jack teacher male
Jack teacher male
Jack teacher male
我想讓這 3 行如下：
name identity gender
Mary1 student female
Mary2 student female
Mary3 student female
Jack1 teacher male
Jack2 teacher male
Jack3 teacher male
我怎么能做到？謝謝
我嘗試使用創建函式 tvValues，但它不起作用。
uj5u.com熱心網友回復：
一種方法使用row_number()：
select t.*,
case when count(*) over(partition by name, identity, gender) > 1
then concat(name, row_number() over(partition by name, identity, gender order by name))
else name
end as new_name
from mytable t
這會將序列號附加到屬于重復行的名稱。
我們可以使用可更新的 CTE 將陳述句轉換為更新：
with cte as (
select name,
count(*) over(partition by name, identity, gender) cnt,
row_number() over(partition by name, identity, gender order by name) rn
from mytable
)
update cte
set name = concat(name, rn)
where cnt > 1
uj5u.com熱心網友回復：
這可以通過ROW_NUMBER() OVER(ORDER BY NAME, IDENTITY, GENDER)
如果需要使用ASC/進行調整，您可以更改您的訂單DESC。
所以你是 SELECT 可以開始：
SELECT CONCAT(NAME, ROW_NUMBER() OVER(ORDER BY NAME, IDENTITY, GENDER)), IDENTITY, GENDER FROM …
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/524329.html
上一篇：在現有查詢中尋求幫助以洗掉重復項