22,302
社区成员




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