把一表中某一字段相同 的所有记录列出!!
意思是只要有重复,就列出。。!!
id name ......
1 asd
2 qwe
3 qwe
4 dsf
5 asd
一sql列出id name ....
1 asd
5 asd
2 qwe
4 qwe
意思是只要有重复,就列出。。!!
id name ......
1 asd
2 qwe
3 qwe
4 dsf
5 asd
一sql列出id name ....
1 asd
5 asd
2 qwe
4 qwe
inner join
(select name from tt group by name having count(*)>=2) b
on a.name=b.name order by name,id
select a.id,a.name from tt a
left join tt b on a.name=b.name
group by a.id,a.name
having count(b.id)>=2
create table tb(id int, name varchar(100));
insert tb
select 1, 'asd' union all
select 2, 'qwe' union all
select 3, 'qwe' union all
select 4, 'dsf' union all
select 5, 'asd' --查询
select
*
from
tb t
where
(select count(1) from tb where name=t.name)>1/**结果id name
1 asd
2 qwe
3 qwe
5 asd **/ --删除测试环境
drop table tb
left join tt b on a.name=b.name
group by a.id,a.name
having count(b.id)>=2
ORDER BY A.NAME,A.ID
如果是这样的结果name asd
qwe该怎么写。!
谢谢。。
from yourTable
group by name
having count(*)>1
select
distinct
name
from
tb t
where
(select count(1) from tb where name=t.name)>1