如何构建这个SQL语句啊，高手请进！！（百分题）

[cimzzxiang]

表X_table，字段: ID，姓名（NAME），身份证（SFZ），录入时间（TIME），共有记录20万条。
实现目标：将身份证（SFZ）相同的记录查找出来。
如：里面有两个人有重复记录，其中张三（330101……）有2条，李四（330102……）有3条，把这两人所有的重复记录都查出来。
请问如何高效构造这个SQL语句??

[子陌红尘]

如果ID字段不重复：
select a.* from X_table a where exists(select 1 from X_table where SFZ=a.SFZ and ID!=a.ID)

[子陌红尘]

select
a.*
from
X_table a,
(select SFZ from X_table group by SFZ having count(*)>1) b
where
a.SFZ=b.SFZ

[todouwang]

select sfz,id,name,time,count(*) from xtable
group by sfz,id,name,time
having count(*)>1

[$扫地僧$]

select T.*
from
X_table T
where
exists (select 1 from
(select SFZ from X_table group by X_table having count(*)>1) A where T.SFZ=SFZ)

[$扫地僧$]

select T.*
from
X_table T
where
exists (select 1 from
(select SFZ from X_table group by X_table having count(*)>1) A where T.SFZ=SFZ)

[-狙击手-]

declare @t table(id int,sfz varchar(18))
insert @t
select 1,'121212' union all
select 2,'121212' union all
select 3,'121211'



select *
from @t
where sfz in(select sfz from @t group by sfz having count(sfz) > 1)

/*


id sfz
----------- ------------------
1 121212
2 121212

*/

[zhouhaihe]

select SFZ from X_table group by SFZ having count(SFZ)>1

[gdgf]

select * from X_table where SFZ in (select SFZ from X_table group by SFZ having count(*)>1)

[cimzzxiang]

各位能不能详细解释一下你们写的语句吗？（我是新手，不好意思 :)

[TheSon]

select * from X_table where SFZ in (
select SFZ from X_table grouP by SFZ having count(*)>1)

[子陌红尘]

如果当前记录存在SFZ相同且ID不同的记录，则输出该记录
select a.* from X_table a where exists(select 1 from X_table where SFZ=a.SFZ and ID!=a.ID)

[weinickli]

select i.*,j.rsfz from X_table as i inner join
(select SFZ,count(SFZ) as rsfz from X_table group by SFZ having rsfz >1) as j
on i.SFZ=j.SFZ
不提出重复记录用字查询就行了
要提也就写全吧```还得出来重复次数

[cimzzxiang]

请问libin_ftsafe(子陌红尘) ，用存储过程怎么写？