当前位置: 代码迷 >> Oracle技术 >> 去重复有关问题
  详细解决方案

去重复有关问题

热度:91   发布时间:2016-04-24 08:29:12.0
去重复问题
表A
 id name idCardNo
  1 a 11111
  2 b 22222
表B
 id name idCardNo
  1 c 11111
  2 d 22222
表C
 id name idCardNo
  1 a 11112
  2 f 22222

 select distinct idCardNo,id,name from(
  select idCardNo,id,name from A
  union
  select idCardNo,id,name from B
  union
  select idCardNo,id,name from C

 )

怎么才能只根据idCardNo去重复结果为:
1 a 11111
1 a 11112
2 b 22222

------解决方案--------------------
select min(id) as id,min(name) as name,idCardNo from(
select idCardNo,id,name from aa
union
select idCardNo,id,name from bb
union
select idCardNo,id,name from cc
 ) group by idCardNo
 order by idcardno

------解决方案--------------------
SQL code
select id ,name ,idCardNofrom (select row_number()over(partition by idCardNo order by name asc) rn,id ,name ,idCardNo from(select idCardNo,id,name from A  union all  select idCardNo,id,name from B  union all  select idCardNo,id,name from C))where rn=1
------解决方案--------------------
探讨

SQL code
select id ,name ,idCardNofrom
(select row_number()over(partition by idCardNo order by name asc) rn,
id ,name ,idCardNo from
(select idCardNo,id,name from A
union all
select idCardNo,id……
  相关解决方案