如果用SQL语句显示一个表里重复的所有记录

2025-02-27 20:45:52

推荐回答（4个）

回答1：

lqefn写的在oracle和sql server下都能运行。
我写一个在oracle下效率略微高一些的：
select * from 表1 a
where exists(select 1 from 表1 b where a.a = b.a and a.b = b.b and a.c = b.c and a.rowid<>b.rowid)

回答2：

select * from 表1 as 表a where exists(select a,b,c from 表1 as 表b where 表a.a = 表b.a and 表a.b = 表b.b and 表a.c = 表b.c group by a,b,c having count(*) > 1)

回答3：

select * from 表1
where (a=c) AND (c=e)

回答4：

select t1.* from 表1 t1,表1 t2
where t1.主键<>t2.主键//主键需要不相等
and t1.a = t2.a
and t1.c = t2.c
and t1.e = t2.e