--参考: SQL> with temp as( 2 select 'A' f,'B' t,1 r from dual union all 3 select 'B','A',2 from dual union all 4 select 'C','D',1 from dual) 5 select a.f id_1,a.t id_2, 6 a.f||'->'||a.t||':'||a.r||','||b.f||'->'||b.t||':'||b.r relation 7 from temp a,temp b 8 where a.f=b.t 9 and a.t=b.f 10 union all 11 select c.f,c.t,c.f||'->'||c.t||':'||c.r 12 from temp c 13 /
ID_1 ID_2 RELATION ---- ---- -------------------------------------------------------------------------------- B A B->A:2,A->B:1 A B A->B:1,B->A:2 A B A->B:1 B A B->A:2 C D C->D:1
SELECT t2.from_id as id1,t2.to_id as id2 , t2.from_id||'->'||t2.to_id||':'||t2.relation|| (select ','||t3.from_id||'->'||t3.to_id||':'||t3.relation from relation_test t3 where t2.to_id=t3.from_id ) FROM relation_test t2 WHERE ROWID IN ( select MIN(ROWID) from relation_test t1 GROUP BY to_number(T1.FROM_ID,'XX')+ to_number(T1.TO_ID,'XX') ) order by t2.from_id表名换一下,做的不好,有局限性,勉强通过,要停电了,有空再完善.
你的查询结果中能过滤掉第二条和第四条么?比如:不管是A->B还是B->A都是表示A和B有关系,只需要得到一条记录就可以了,A B A->B:1,B->A:2我真是找不到头绪,非常感谢
with temp as( select 'A' f,'B' t,1 r from dual union all select 'B','A',2 from dual union all select 'C','D',1 from dual) select a.f id_1,a.t id_2, a.f||'->'||a.t||':'||a.r||','||b.f||'->'||b.t||':'||b.r relation from temp a,temp b where a.f=b.t and a.t=b.f and a.f < b.f union all select c.f,c.t,c.f||'->'||c.t||':'||c.r from temp c where not exists (select 1 from temp d where d.f=c.t and d.t=c.f)
select id_1, id_2, wm_concat(x) relation from( select least(f, t) id_1, greatest(f, t) id_2, f || '->' || t || ':' || r x from relation) group by id_1, id_2;
SQL> select * from relation;
FROM_ID TO_ID RELATION ---------- ---------- ---------- A B 1 B A 2 C D 1
SQL> SQL> select a.from_id,b.to_id, 2 a.from_id || '->' || a.to_id || ':' || a.relation || ( 3 case when b.from_id is null then null 4 else ',' || b.from_id || '->' || b.to_id || ':' || b.relation end) 5 from (select * from relation where from_id < to_id) a 6 left join (select * from relation where from_id > to_id) b 7 on a.from_id = b.to_id;
FROM_ID TO_ID A.FROM_ID||'->'||A.TO_ID||':'| ---------- ---------- -------------------------------------------------------------------------------- A A A->B:1,B->A:2 C C->D:1
SQL>
7楼的思维确实不错 照着做了一个 select a.from_id, a.to_id, a.from_id || '->' || a.to_id || ':' || a.relation || ',' || decode(b.from_id, null, null, b.from_id || '->' || b.to_id || ':' || b.relation) from (select * from relation where from_id < to_id) a left join (select * from relation where from_id > to_id) b on a.from_id = b.to_id; FROM_ID TO_ID A.FROM_ID||'->'||A.TO_ID||':'| 1 A B A->B:1,B->A:2 2 C D C->D:1,
--参考:
SQL> with temp as(
2 select 'A' f,'B' t,1 r from dual union all
3 select 'B','A',2 from dual union all
4 select 'C','D',1 from dual)
5 select a.f id_1,a.t id_2,
6 a.f||'->'||a.t||':'||a.r||','||b.f||'->'||b.t||':'||b.r relation
7 from temp a,temp b
8 where a.f=b.t
9 and a.t=b.f
10 union all
11 select c.f,c.t,c.f||'->'||c.t||':'||c.r
12 from temp c
13 /
ID_1 ID_2 RELATION
---- ---- --------------------------------------------------------------------------------
B A B->A:2,A->B:1
A B A->B:1,B->A:2
A B A->B:1
B A B->A:2
C D C->D:1
t2.from_id||'->'||t2.to_id||':'||t2.relation||
(select ','||t3.from_id||'->'||t3.to_id||':'||t3.relation
from relation_test t3 where t2.to_id=t3.from_id
)
FROM relation_test t2 WHERE ROWID IN (
select
MIN(ROWID)
from relation_test t1
GROUP BY to_number(T1.FROM_ID,'XX')+ to_number(T1.TO_ID,'XX')
)
order by t2.from_id表名换一下,做的不好,有局限性,勉强通过,要停电了,有空再完善.
你的查询结果中能过滤掉第二条和第四条么?比如:不管是A->B还是B->A都是表示A和B有关系,只需要得到一条记录就可以了,A B A->B:1,B->A:2我真是找不到头绪,非常感谢
select 'A' f,'B' t,1 r from dual union all
select 'B','A',2 from dual union all
select 'C','D',1 from dual)
select a.f id_1,a.t id_2,
a.f||'->'||a.t||':'||a.r||','||b.f||'->'||b.t||':'||b.r relation
from temp a,temp b
where a.f=b.t
and a.t=b.f
and a.f < b.f
union all
select c.f,c.t,c.f||'->'||c.t||':'||c.r
from temp c where not exists (select 1 from temp d where d.f=c.t and d.t=c.f)
from(
select least(f, t) id_1, greatest(f, t) id_2, f || '->' || t || ':' || r x
from relation)
group by id_1, id_2;
FROM_ID TO_ID RELATION
---------- ---------- ----------
A B 1
B A 2
C D 1
SQL>
SQL> select a.from_id,b.to_id,
2 a.from_id || '->' || a.to_id || ':' || a.relation || (
3 case when b.from_id is null then null
4 else ',' || b.from_id || '->' || b.to_id || ':' || b.relation end)
5 from (select * from relation where from_id < to_id) a
6 left join (select * from relation where from_id > to_id) b
7 on a.from_id = b.to_id;
FROM_ID TO_ID A.FROM_ID||'->'||A.TO_ID||':'|
---------- ---------- --------------------------------------------------------------------------------
A A A->B:1,B->A:2
C C->D:1
SQL>
照着做了一个
select a.from_id, a.to_id, a.from_id || '->' || a.to_id || ':' || a.relation || ',' ||
decode(b.from_id, null, null, b.from_id || '->' || b.to_id || ':' || b.relation)
from (select * from relation where from_id < to_id) a
left join (select * from relation where from_id > to_id) b
on a.from_id = b.to_id;
FROM_ID TO_ID A.FROM_ID||'->'||A.TO_ID||':'|
1 A B A->B:1,B->A:2
2 C D C->D:1,