MySQL 查询重复的数据
我有这样的一个表
id | name | value |
---|---|---|
1 | 张三 | 100 |
2 | 张三 | 100 |
3 | 李四 | 200 |
4 | 王麻子 | 300 |
我想查询name
重复数据有多少条,并且是哪个name
重复了
SELECT `name`,COUNT(*) FROM tables GROUP BY `name` HAVING COUNT(*)>1;
我想要知道具体是那些数据重复了
SELECT * FROM tables WHERE `name` IN (SELECT `name` FROM tables GROUP BY `name` HAVING COUNT(*)>1) ORDER BY `name` ASC;
我想查出所有重复数据,并排除最小id
SELECT * FROM tables WHERE `name` IN (SELECT `name` FROM tables GROUP BY `name` HAVING COUNT(*)>1) AND id NOT IN (SELECT MIN(id) FROM tables GROUP BY `name` HAVING COUNT(*)>1) ORDER BY `name` ASC;
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: