删除重复“对比”的字段。

[zhuruijay]

test 表
一个字段 name
记录
a
b
c
d

查询不重复对比的次数
a b
a c
a d
b c
b d
c d
应该怎么去掉ab ba这样的重复对比？

[kelph]

create table a(a char(1))
insert a select 'a' union all select 'b' union all select 'c' union all select 'd'
select * from a,a t where  a.a<t.a

[kelph]

a    a    
---- ---- 
a    b
a    c
b    c
a    d
b    d
c    d

（所影响的行数为 6 行）

[dawugui]

create table tb(name varchar(10))
insert into tb values('a')
insert into tb values('b')
insert into tb values('c')
insert into tb values('d')
go

select m.name , n.name from tb m , tb n where m.name < n.name order by m.name

drop table tb

/*
name       name       
---------- ---------- 
a          b
a          c
a          d
b          c
b          d
c          d
（所影响的行数为 6 行）
*/

[zbc1009]

create table test(a char(1))
go
insert into test values('a')
go
insert into test values('b')
go
insert into test values('c')
go
insert into test values('d')
go

select distinct
(case when t1.a<=t2.a then t1.a else t2.a end) as col1, 
(case when t1.a>t2.a then t1.a else t2.a end) as col2
from test t1 cross join test t2
where t1.a<>t2.a

[Limpire]

--> 测试数据: @T
declare @T table (name varchar(1))
insert into @T
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'

select * from @T a cross join @T b where a.name<b.name order by 1

/*
a	b
a	c
a	d
b	c
b	d
c	d
*/