100分求Sql语句,谢谢

[haoliqi]

表结构如下
create table AB ( A varchar(20),B varchar(20),C varchar(20))

insert AB select 'a','b','e'
insert AB select 'b','a','e'
insert AB select 'a','c','e'
insert AB select '3','b','f'
insert AB select 'b','3','f'
insert AB select 'b','3','d'

要求查询出结果如下:
a,b,e
b,a,e
3,b,f
b,3,f
小弟只会限制了A列和B 列的条件
select * from AB Where AB.A in(select AB.B from AB)
and AB.B in (Select AB.A from AB)
不知C列条件如何限制,查询出的一定是偶数行,且每两行的A列与B列互相相同,C列相等
请高手指教,在线等,马上结贴!

[haoliqi]

UP

[zjcxc]

select * from AB a
where exists(
select * from AB
where A=a.B and B=a.A and C=a.C)

[pbsql]

select * from AB t Where exists(select * from AB where a=t.b and b=t.a and c=t.c)

[zjcxc]

create table AB ( A varchar(20),B varchar(20),C varchar(20))
insert AB select 'a','b','e'
insert AB select 'b','a','e'
insert AB select 'a','c','e'
insert AB select '3','b','f'
insert AB select 'b','3','f'
insert AB select 'b','3','d'
go

select * from AB a
where exists(
select * from AB
where A=a.B and B=a.A and C=a.C)
go

--删除测试
drop table AB

/*--测试结果

A B C
-------------------- -------------------- --------------------
a b e
b a e
3 b f
b 3 f

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

[Andy__Huang]

改一下

create table AB ( A varchar(20),B varchar(20),C varchar(20))
insert AB select 'a','b','e'
insert AB select 'b','a','e'
insert AB select 'a','c','e'
insert AB select '3','b','f'
insert AB select 'b','3','f'
insert AB select 'b','3','d'

select a.* from AB a inner join AB b on a.A=b.B and a.B=b.A and a.C=b.C

--結果
A B C
--------------------
a b e
b a e
3 b f
b 3 f

[davorsuker39]

select * from AB a
where exists(
select * from AB
where A=a.B and B=a.A and C=a.C)

[lsxaa]

学习

[jieyh]

create table #AB ( A varchar(20),B varchar(20),C varchar(20))

insert #AB select 'a','b','e'
insert #AB select 'b','a','e'
insert #AB select 'a','c','e'
insert #AB select '3','b','f'
insert #AB select 'b','3','f'
insert #AB select 'b','3','d'

SELECT a.a,a.b,a.c FROM #AB a CROSS JOIN #AB b
where a.a=b.b and b.a=a.b and a.c=b.c

drop TABLE #AB