发帖..发一个就可以了.


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 行）
*/

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (C1 int,C2 varchar(1))
insert into #T
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'C' union all
select 1,'D' union all
select 2,'D' union all
select 1,'E'

select distinct * from #T as t where (select count(distinct c1) from #T where c2=t.c2)=2 order by 2

/*
C1          C2
----------- ----
1           B
2           B
1           D
2           D
*/

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)

select * from @t a where 字段1 = 1 and exists(select 1 from @t where 字段1 =2 and  字段2=a.字段2)

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 left join
(select col2 from tb group by col2 having count(*)>=2) as a on a.col2=tb.col2
where a.col2 is not null

drop table tb

select a.字段1,a.字段２
(
    select 字段１,字段２ from tab where 字段１=1  
)as a
inner join
(
    select 字段１,字段２ from tab where 字段１=2
)as b
on a.字段２ = b.字段２
union
select b.字段1,b.字段２
(
    select 字段１,字段２ from tab where 字段１=1  
)as a
inner join
(
    select 字段１,字段２ from tab where 字段１=2
)as b
on a.字段２ = b.字段２

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