表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
------解决方案--------------------