表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
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 idCardNo,id,name from a
union
select idCardNo,id,name from b
union
select idCardNo,id,name from c
) group by idCardNo
order by idcardno
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
(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