db2和oralce：用分析函数代替自连接

db2自连接用分析函数代替 

例如:

create table TEST
(
SNO    VARCHAR2(30),
SEQ_NUM    VARCHAR2(30),
CARD VARCHAR2(500)
);
insert into TEST values('stu1','1','ckzh11');
insert into TEST values('stu1','1','ckzh12');
insert into TEST values('stu2','2','ckzh21');
insert into TEST values('stu2','2','ckzh22');
insert into TEST values('stu2','2','ckzh23');
insert into TEST values('stu3','3','ckzh31');
insert into TEST values('stu4','4','ckzh41');

我想找出SNO ,SEQ_NUM只对一个CARD 的全表信息

由于之前并不清楚分析函数.写了一个自连接,个人觉得非常的麻烦，同时当表比较大的时候，也容易影响效率

select 

TEST.* from test A

left join 

 (select SNO   ,

SEQ_NUM    ,

count(*)

group by  

SNO  ,

SEQ_NUM    

having count(*)>1) B

on A.SNO =B.SNO   ;

改下成分析函数写法如下：

select * from (select CARD, SNO,SEQ_NUM,
 count(*) 
over(partition by SNO,SEQ_NUM) rn from TEST)a
where rn=1 

一步到位呐