如果記錄重複，則查詢以連接列

我的表有兩列，即 id 和 name。如果兩個以上的名稱相同，我想連接列：

id                                                                    name
1                                                                     John
2                                                                     David
3                                                                     Zed
4                                                                     John
5                                                                     David
6                                                                     John
7                                                                     David
8                                                                     John
9                                                                     John

現在我希望我的輸出如果名稱重複，則第一條記錄將保持原樣，其他記錄將通過 id 連接，例如

Id                                                                     Name
1                                                                      John
2                                                                      David
3                                                                       Zed
4                                                                      4 John
5                                                                     5 David
6                                                                     6 John 
7                                                                      7 David
8                                                                      8 John
9                                                                      9 John

誰能幫忙…謝謝

partition by在列上使用name，並根據id-s的順序計算行數。如果實際行的行號大於 1，則連接idand name，否則只需使用name.

with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select 
 id, 
 case 
   when row_number() over (partition by name order by id) > 1 then id || ' ' || name
   else name
 end as name
from t order by id;

       ID NAME
---------- ----------------------------------------------
        1 john
        2 david
        3 zed
        4 4 john
        5 5 david
        6 6 john
        7 7 david
        8 8 john
        9 9 john

9 rows selected.

要平等對待john、JOHN、joHN等，只需更改partition by列，例如upper(name)。

select 
 id, 
 case 
   when row_number() over (partition by upper(name) order by id) > 1 then id || ' ' || name
   else name
 end as name
from t order by id;

引用自：https://dba.stackexchange.com/questions/109077