请教论坛里各位高手一个问题:某个数据集包含数据4列,itme1,value1,item2,value2
数据为:I1,1,I2,1
I1,1,I3,1
I1,1,I5,1
I2,1,I3,1
I2,1,I4,1
I2,1,I5,1请教如何写sql可以实现数据集的自连接,输出结果不重复,如以上数据集应输出:
I1,1,I2,1,I3,1
I1,1,I2,1,I4,1
I1,1,I2,1,I5,1
I2,1,I3,1,I4,1
I2,1,I3,1,I5,1
I2,1,I4,1,I5,1
数据为:I1,1,I2,1
I1,1,I3,1
I1,1,I5,1
I2,1,I3,1
I2,1,I4,1
I2,1,I5,1请教如何写sql可以实现数据集的自连接,输出结果不重复,如以上数据集应输出:
I1,1,I2,1,I3,1
I1,1,I2,1,I4,1
I1,1,I2,1,I5,1
I2,1,I3,1,I4,1
I2,1,I3,1,I5,1
I2,1,I4,1,I5,1
(
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
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)
goselect 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.itemdrop 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 行)
*/