select a.* from tablename a, (select left(id,3) as id from tablename group by left(id,3) having count(*)>1) b where left(a.id,3)=b.id
非常感谢,现在我想,将出现的多条记录删除,只保留一条,并将其id更改为前三位 结果如下所示: id 123 125
select * from tb a where exists(select left(id,3) from tb where left(id,3)=left(a.id,3) group by left(id,3) having count(*)>1)
select distinct left(a.id,3) from tablename a, (select left(id,3) as id from tablename group by left(id,3) having count(*)>1) b where left(a.id,3)=b.id
from tablename a,
(select left(id,3) as id
from tablename
group by left(id,3)
having count(*)>1) b
where left(a.id,3)=b.id
结果如下所示:
id
123
125
from tb a
where exists(select left(id,3) from tb where left(id,3)=left(a.id,3)
group by left(id,3) having count(*)>1)
from tablename a,
(select left(id,3) as id
from tablename
group by left(id,3)
having count(*)>1) b
where left(a.id,3)=b.id