用分析函数分析每行里面列值重复的列的最大个数
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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7916042/viewspace-918952/ ，如需转载，请注明出处，否则将追究法律责任。
