Question

sql中如何查询一个表中重复的数据，并且重复了几次？

select col001 as ufo from lhsjb group by col001 用这个我可以查出来重复的数据，但是不知道各个都重复几次

select count(col001)as ufo from lhsjb group by col001 用这个我能查出有多少数据重复了几次，但是显示不出来是哪几个数据

但是我想用一个语句查询在lhsjb这个表中的col001列都有哪些数据重复并且重复了几次，如何写？

Answer 1

select col001,sum(1) as ufo from lhsjb group by col001 having sum(1) > 1

Answer 2

select col001，count(col001) from lhsjb where count(col001) > 1 group by col001

Answer 3

select col001,count(col001) from lhsjb where coloo1>1 group by col001

Answer 4

select col001,count(0) as counter from lhsjb group by col001 having counter >1

Answer 5

select col001 as ufo,sum(1) from lhsjb group by col001