select id from table group by id having count(id)>1
#创建test表 DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `ID` int(11) default '0', `name` varchar(255) NOT NULL default 'name' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;#添加模拟数据 INSERT INTO `test` VALUES (1,'name'); INSERT INTO `test` VALUES (4,'name'); INSERT INTO `test` VALUES (3,'name'); INSERT INTO `test` VALUES (4,'name'); INSERT INTO `test` VALUES (4,'name'); INSERT INTO `test` VALUES (4,'name'); INSERT INTO `test` VALUES (3,'name'); INSERT INTO `test` VALUES (5,'name'); INSERT INTO `test` VALUES (3,'name'); INSERT INTO `test` VALUES (2,'name'); INSERT INTO `test` VALUES (1,'name'); INSERT INTO `test` VALUES (1,'name');#条件:查询ID重复条数>1的记录 #测试数据有十条,ID只有2和5不满足 #所以应该除了这两条记录都可以查询到 select test.* from test inner join ( select ID,count(*) num from test group by ID ) tmp where test.ID = tmp.ID and tmp.num >1
select * from tb group by id having count(*)>1这个了
select * from tb where id in (select id from tb group by id having count(1)>1)
select id from tb group by id having count(*)>1
select * from tb group by id having count(*)>1 加分了
select * from table group by id having count(*)>1 重复大于1次的id都会出来
select * from tb group by id >1凡是大于1的都是多出来重复的
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`ID` int(11) default '0',
`name` varchar(255) NOT NULL default 'name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#添加模拟数据
INSERT INTO `test` VALUES (1,'name');
INSERT INTO `test` VALUES (4,'name');
INSERT INTO `test` VALUES (3,'name');
INSERT INTO `test` VALUES (4,'name');
INSERT INTO `test` VALUES (4,'name');
INSERT INTO `test` VALUES (4,'name');
INSERT INTO `test` VALUES (3,'name');
INSERT INTO `test` VALUES (5,'name');
INSERT INTO `test` VALUES (3,'name');
INSERT INTO `test` VALUES (2,'name');
INSERT INTO `test` VALUES (1,'name');
INSERT INTO `test` VALUES (1,'name');#条件:查询ID重复条数>1的记录
#测试数据有十条,ID只有2和5不满足
#所以应该除了这两条记录都可以查询到
select test.* from test
inner join
(
select ID,count(*) num from test group by ID
) tmp
where test.ID = tmp.ID and tmp.num >1
*
from
tb
where
id
in
(select id from tb group by id having count(1)>1)
from tb
group by id
having count(*)>1
重复大于1次的id都会出来