select a.* from tablename a join ( select 姓名, 身份证号 from tablename group by 姓名,身份证号 having count(*) > 1 ) b on a.姓名=b.姓名 and a.身份证号=b.身份证号
select distinct 姓名,身份证号 into tempdb..table1 from tablename delete from tablename insert into tablename select * from tempdb..table1
CREATE TABLE A(姓名 VARCHAR(2),身份证号 VARCHAR(2),住址 VARCHAR(10))TRUNCATE TABLE AINSERT INTO A(姓名,身份证号,住址) SELECT 'A','01','W' UNION ALL SELECT 'A','01','B' UNION ALL SELECT 'X','01','A' UNION ALL SELECT 'C','01','C' UNION ALL SELECT 'D','01','D' UNION ALL SELECT 'B','02','E' UNION ALL SELECT 'B','02','F'SELECT A1.* FROM A A1 INNER JOIN A A2 ON A1.姓名=A2.姓名 AND A1.身份证号=A2.身份证号 WHERE A1.住址>A2.住址
select a.* from 表 a,(select 姓名,身份证号 from 表 group by 姓名,身份证号 having count(*)>1) b where a.姓名=b.姓名 and a.身份证号=b.身份证号
from tablename a join
( select 姓名,
身份证号
from tablename
group by 姓名,身份证号
having count(*) > 1
) b
on a.姓名=b.姓名 and a.身份证号=b.身份证号
delete from tablename
insert into tablename select * from tempdb..table1
CREATE TABLE A(姓名 VARCHAR(2),身份证号 VARCHAR(2),住址 VARCHAR(10))TRUNCATE TABLE AINSERT INTO A(姓名,身份证号,住址) SELECT 'A','01','W'
UNION ALL SELECT 'A','01','B'
UNION ALL SELECT 'X','01','A'
UNION ALL SELECT 'C','01','C'
UNION ALL SELECT 'D','01','D'
UNION ALL SELECT 'B','02','E'
UNION ALL SELECT 'B','02','F'SELECT A1.* FROM A A1 INNER JOIN A A2 ON A1.姓名=A2.姓名 AND A1.身份证号=A2.身份证号 WHERE A1.住址>A2.住址
from 表 a,(select 姓名,身份证号 from 表 group by 姓名,身份证号 having count(*)>1) b
where a.姓名=b.姓名 and a.身份证号=b.身份证号