create table abin6(id integer,
name nvarchar2(100),
score integer,
constraint pk_abin6 primary key(id));
create table abin7(id integer,
address nvarchar2(100),
sid integer,
constraint pk_abin7 primary key(id),
constraint fk_abin7 foreign key (sid) references abin6(id)
);
select * from abin6 t left join abin7 s on t.id=s.sid and t.id=1;
select * from abin6 t left join abin7 s on t.id=s.sid where t.id=1;
select * from abin6 t,abin7 s where t.id=s.sid(+) ;
select * from abin6 t,abin7 s where t.id(+)=s.sid;
select * from abin6 t,abin7 s where s.sid(+)=t.id;
select * from abin6 t,abin7 s where s.sid=t.id(+);
select * from abin6 t inner join abin7 s on t.id=s.sid;
select * from abin6 t union select * from abin7 s where exists (select * from abin6 k where s.sid=k.id and k.id
=1);
select * from abin6 t full join abin7 s on t.id=s.sid;
select * from abin7 s full join abin6 t on s.sid=t.id;
select * from abin6 natural join abin7;
select * from abin6 t cross join abin7;
以下兩句是等價(jià)查詢：
select * from abin6 t where id=1 or id=2;
select * from abin6 t where t.id=1 union all select * from abin6 s where s.id=2;
一。查找重復(fù)記錄
1。查找全部重復(fù)記錄
select * from abin4 s where s.name in (select t.name from abin4 t
group by t.name having count(t.name)>1);
select * from abin4 s where exists (select * from abin4 t where t.name=s.name
group by t.name having count(t.name)>1 );
2。過(guò)濾重復(fù)記錄(只顯示一條)
select * from abin4 s where s.id in (select max(id) from abin4 t group by t.name );
二。刪除重復(fù)記錄
1。刪除全部重復(fù)記錄（慎用）
Delete 表 Where 重復(fù)字段 In (Select 重復(fù)字段 From 表 Group By 重復(fù)字段 Having Count(*)>1)
2。保留一條（這個(gè)應(yīng)該是大多數(shù)人所需要的 ^_^）
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
注：此處保留ID最大一條記錄
http://blog.csdn.net/csskysea/article/details/6987760