Question

oracle求一个查询重复数据SQL语句

有一个CUSTMER表 如下：
IDTP(证件类型) IDNO（证件号码）CUSTNAME(客户姓名)
户口本 123 张三
户口本 321 李四
护照 123 王五
护照 123 赵六
军官证 321 猪七
条件是这样的：找出证件类型相同并且证件号码相同但是姓名不同的记录
谢谢~~

Answer 1

mysql 是这么写的，我没学过orcal语法，你可以类比一下

select * from CUSTMER t1 where exists(

 select 1 from CUSTMER t2

where t1.IDTP=t2.IDTP

and t1.IDNO=t2.IDNO

and t1.CUSTNAME <>t2.CUSTNAME

)

Answer 2

select IDTP,IDNO,CUSTNAME from table where IDTP||IDNO in
(select IDTP||IDNO from table group by IDTP,IDNO having count(*)>=2
minus
select IDTP||IDNO from table group by IDTP||IDNO,CUSTNAME having count(*)=1)
还有其他写法，稍微有点麻烦，而且没有环境没办法测试。

Answer 3

select t1.t1_tp,t1.t1_no,t2.t2_name
from

(select t.idtp as "t1_tp" ,t.idno as "t1_no" ,max(rowid) as "t1_rowid"
from custmer t
group by t.idtp ,t.idno
having count(1)>1)t1,

(select t.custname as "t2_name",max(rowid) as "t2_rowid"
from custmer t
group by t.custname
having count(1)<2 )t2
where t1.t1_rowid=t2.t2_rowid

没运行自己测一下吧