求MYSQL中某字段内的重复数据
数据库: show
数据表: tbl_vod
字段: d_name
查找字段 d_name 中重复的数据
追问:谢谢你,这个语句可以查出来某个数据重复的次数,,我想删除里面的重复保留一个要怎么写呢
或让他全部显示我手工删除 ,现在查询出来只能显示某个数据的重复次数 没办法选择删除我是想把重复的彻的删除掉呀d_id 这个是主键#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(d_id) from tbl_vod group by d_name)' at line 1
d_id是不重复的#1093 - You can't specify target table 'tbl_vod' for update in FROM clause
有5万多数据 有几千条重复吧#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(d_id) from tbl_vod group by d_name )' at line 4
答案:select d_name,count(d_name) from tbl_vod group by d_name having count(d_name)>1
其他:这样就能找出d_name重复的
select d_name
from tbl_vod
group by d_name having count(1) > 1
如果要所有记录
select *
from tbl_vod where d_name in (select d_name
from tbl_vod
group by d_name having count(1) > 1)
上一个:mysql连表查询
下一个:mysql group by怎么使用的?