我在Oracle中有以下表格:
Id Acct Name
==================
1 123 Anyone
1 234 Anyone
2 435 Someone
2 675 Someone
2 732 Someone
3 765 Anonymous
4 987 Hidden
4 987 Hidden
我需要以下输出:
Id Acct1 Acct2 Name
==========================
1 123 234 Anyone
2 435 675 Someone
2 435 732 Someone
所以,我只需要显示账号不匹配的记录,但如上所述,两个账号都显示在一行中。有人能帮忙吗?
使用自联接:
select t1.id, t1.name, t1.acct, t2.acct
from t t1 join
t t2
on t1.id = t2.id and t1.name = t2.name and t1.acct <> t2.acct;
或者,如果你可以有两个以上的账户,那么listagg()
可能更合适:
select t.id, t.name, listagg(acct, ',') within group (order by acct)
from t
group by t.id, t.name
having min(acct) <> max(acct);