如何比较身份证号，提取有超过12位身份证字符相同的记录

Question

select sum(case when substring(字段1,1,1)=substring(字段2,1,1) then 1 when substring(字段1,2,1)=substring(字段2,2,1) then 1 ...when substring(字段1,15,1)=substring(字段2,15,1) then 1 else 0 end) >=12

Answer 1

group by left(身份证号,12)

Answer 2

select *
  from 表
 where left(身份证号, 12) in (select left(身份证号, 12)
                                from 表
                               group by
                                left(身份证号, 12)
                              having count(*) > 1)

Answer 3

假设身份证号都是15位
select 身份证号 from tb group by left(身份证号,12) having count(*) > 1 假设身份证号是15位或18位select 身份证号 from 
(
  select 身份证号 from tb where len(身份证号) = 15
  union all
  select substring(身份证号,1,6) + substring(身份证号,9,9) as 身份证号 from tb where len(身份证号) = 18
) t
group by left(身份证号,12)

Answer 4

SELECT a.id, b.id
FROM table1 a, table2 b
WHERE (  Abs(sign(substring(a.id,1,1)-substring(b.id,1,1)))
+ Abs(sign(substring(a.id,2,1)-substring(b.id,2,1)))
+ Abs(sign(substring(a.id,3,1)-substring(b.id,3,1)))
+ Abs(sign(substring(a.id,4,1)-substring(b.id,4,1)))
+ Abs(sign(substring(a.id,5,1)-substring(b.id,5,1)))
+ Abs(sign(substring(a.id,6,1)-substring(b.id,6,1)))
+ Abs(sign(substring(a.id,7,1)-substring(b.id,7,1)))
+ Abs(sign(substring(a.id,8,1)-substring(b.id,8,1)))
+ Abs(sign(substring(a.id,9,1)-substring(b.id,9,1)))
+ Abs(sign(substring(a.id,10,1)-substring(b.id,10,1)))
+ Abs(sign(substring(a.id,11,1)-substring(b.id,11,1)))
+ Abs(sign(substring(a.id,12,1)-substring(b.id,12,1)))
+ Abs(sign(substring(a.id,13,1)-substring(b.id,13,1)))
+ Abs(sign(substring(a.id,14,1)-substring(b.id,14,1)))
+ Abs(sign(substring(a.id,15,1)-substring(b.id,15,1)))  ) = 3
AND Len(a.id)=15
AND Len(b.id)=15