请教一个用sql语句实现数据集的自连接问题

create table tb (itme1 varchar(10),value1 int,item2 varchar(10),value2 int)

insert into tb values('I1',1,'I2',1)

insert into tb values('I1',1,'I3',1)

insert into tb values('I1',1,'I5',1)

insert into tb values('I2',1,'I3',1)

insert into tb values('I2',1,'I4',1)

insert into tb values('I2',1,'I5',1)

go



select distinct t1.* , t2.* , t3.* from

(

select itme1 item, value1 value from tb

union 

select item2 item, value2 value from tb

) t1,

(

select itme1 item, value1 value from tb

union 

select item2 item, value2 value from tb

) t2,

(

select itme1 item, value1 value from tb

union 

select item2 item, value2 value from tb

) t3

where t1.item < t2.item and t2.item < t3.item

order by t1.item , t2.item , t3.item



drop table tb



/*

item       value       item       value       item       value       

---------- ----------- ---------- ----------- ---------- ----------- 

I1         1           I2         1           I3         1

I1         1           I2         1           I4         1

I1         1           I2         1           I5         1

I1         1           I3         1           I4         1

I1         1           I3         1           I5         1

I1         1           I4         1           I5         1

I2         1           I3         1           I4         1

I2         1           I3         1           I5         1

I2         1           I4         1           I5         1

I3         1           I4         1           I5         1



（所影响的行数为 10 行）

*/

select distinct t1.* , t2.* , t3.* from

(

select itme1 itme, value1 value from tb

union 

select itme2 itme, value2 value from tb

) t1,

(

select itme1 itme, value1 value from tb

union 

select itme2 itme, value2 value from tb

) t2,

(

select itme1 itme, value1 value from tb

union 

select itme2 itme, value2 value from tb

) t3

where t1.item < t2.item and t2.item < t3.item