create table tb(col1 int,col2 nvarchar(5))
insert into tb select 1,'Ａ'  
insert into tb select 1,'Ｂ'  
insert into tb select 2,'Ｂ'  
insert into tb select 2,'Ｃ'  
insert into tb select 1,'Ｄ'  
insert into tb select 2,'Ｄ'  
insert into tb select 1,'Ｅ' 

select * 
from tb 
where col2 in(select col2 from tb group by col2 having count(*)>=2)

drop table tb

/*
col1        col2  
----------- ----- 
1           Ｂ
2           Ｂ
1           Ｄ
2           Ｄ

（所影响的行数为 4 行）
*/

select distinct * from tablename as t where (select count(distinct column_1) from tablename where column_2=t.column_2)=2

declare @t table(字段1 int,字段2 varchar)
insert into @t select 1,'A'
insert into @t select 1,'B'
insert into @t select 2,'B'
insert into @t select 3,'C'
insert into @t select 1,'D'
insert into @t select 2,'D'
insert into @t select 1,'E'


select * from @t where 字段2 in(select 字段2 from @t group by 字段2 having count(1)>1)

--这样才对
declare @t table(字段1 int,字段2 varchar)
insert into @t select 1,'A'
insert into @t select 1,'B'
insert into @t select 2,'B'
insert into @t select 3,'C'
insert into @t select 1,'D'
insert into @t select 2,'D'
insert into @t select 1,'E'
insert into @t select 3,'E'


select * from @t where 字段2 in
(select 字段2 from @t where 字段1 in(1,2) group by 字段2 having count(1)>1)