用分析函式分析每行裡面列值重複的列的最大個數

http://www.itpub.net/showthread.php?s=&threadid=792926&perpage=10&pagenumber=1

上面有道題,我想可以這樣利用分析函式來處理

測試用表；
create table uss
(
a1 varchar2(10),
a2 varchar2(10),
a3 varchar2(10),
a4 varchar2(10),
a5 varchar2(10),
a6 varchar2(10),
a7 varchar2(10),
a8 varchar2(10)
)
nologging;
一：對於1
A：插入測試資料：
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','3','3','2','2','3','4');
insert into uss values('1','1','3','3','2','2','3','4');
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','6','1','2','1','3','4');
insert into uss values('1','1','1','6','2','1','3','4');
commit;

這裡有2點:

a. 行列轉換

b. 用分析函式

select rn,a "重複的數字",cn "重複的次數"
from (
select rn,a,count(a) cn ,rank() over (partition by rn order by count(a) desc) area_rank
from (
select rownum rn,a1 as a from uss union all
select rownum rn,a2 as a from uss union all
select rownum rn,a3 as a from uss union all
select rownum rn,a4 as a from uss union all
select rownum rn,a5 as a from uss union all
select rownum rn,a6 as a from uss union all
select rownum rn,a7 as a from uss
) group by rn,a
) where area_rank=1;

[@more@]