select id,name from table group by id,name having count(id)>1
select * from table where id in (select min(id) from table gorup by name)
select distinct * from mytable where count(id)>1
select distinct * from mytable where count(id)>1
--tryCreate table Test(id int,name char(8)) insert into test select 1,'zhou' union all select 2,'wu' union all select 2,'wu' union all select 3,'zheng' union all select 3,'zheng'select id,name from test group by id,name having count(id)>1id name ----------- -------- 2 wu 3 zheng (所影响的行数为 2 行)
select * from tablename where id in ( select id from tablename group id having count(*)>=2 ) B
自己试试看。
select id,name from table group by id,name having count(id)>1
insert into test select 1,'zhou'
union all select 2,'wu'
union all select 2,'wu'
union all select 3,'zheng'
union all select 3,'zheng'select id,name from test group by id,name having count(id)>1id name
----------- --------
2 wu
3 zheng (所影响的行数为 2 行)
where id in
(
select id from tablename
group id
having count(*)>=2
) B