还不太好描述,比如下面两条记录 ID NO DI TYPE 100 13902145214 101 1 (record1) 101 010-88232266 100 2 (record2) 比如上面是两条记录,满足record1.ID=record2.DI and record1.DI=record2.ID
用表自身关联, 如表名为 tableName: select t1.NO, t2.NO from tableName t1, tableName t2 where t1.ID = t2.DI and t2.ID = t1.DI and t1.type=1 and t2.type= 2and t1.type=1 and t2.type= 2 是为了去除重复
with A as ( select '100' ID , '13902145214' NO, '101' DI, '1' TYPE FROM DUAL UNION ALL select '101' ID , '010-88232266' NO, '100' DI, '2' TYPE FROM DUAL UNION ALL select '100' ID , '12541545254' NO, '105' DI, '1' TYPE FROM DUAL UNION ALL select '101' ID , '028-85421142' NO, '106' DI, '2' TYPE FROM DUAL )select A.NO CELLPHONE,B.NO CELLPHONE from A ,A B WHERE A.ID=B.DI AND A.TYPE='1' AND B.TYPE='2' 确实
SELECT NO FROM TABLE START WITH ID=100 CONNECT WITH PRIOR ID=DI
select no as cellphone, (select no from tablename b where a.type=b.id and a.id = b.type) as TELEPHONE from tablename a
select t1.NO, t2.NO from (select * from tableName where tableName.type=1) t1 outer join (select * from tableName where tableName.type=2) t2 ON t1.ID = t2.DI and t2.ID = t1.DI
好像有点错误,能再确认一下吗:ORA-00933: SQL command not properly ended
with A as ( select '100' ID,'13902145214' NO,'101' DI,'1' TYPE FROM DUAL UNION ALL select '101' ID,'010-88232266' NO,'100' DI, '2' TYPE FROM DUAL UNION ALL select '100' ID,'12541544254' NO,'105' DI, '1' TYPE FROM DUAL UNION ALL select '101' ID, '028-85421142' NO,'106' DI,'2' TYPE FROM DUAL ) select t1.NO,t2.NO from (select * from A where A.TYPE='1') t1 full outer join (select * from A where A.TYPE='2') t2 on t1.ID=t2.DI AND t2.ID=t1.DI
ID NO DI TYPE
100 13902145214 101 1 (record1)
101 010-88232266 100 2 (record2)
比如上面是两条记录,满足record1.ID=record2.DI and record1.DI=record2.ID
select t1.NO, t2.NO from tableName t1, tableName t2
where t1.ID = t2.DI and t2.ID = t1.DI
and t1.type=1 and t2.type= 2and t1.type=1 and t2.type= 2 是为了去除重复
with A as
(
select '100' ID , '13902145214' NO, '101' DI, '1' TYPE FROM DUAL
UNION ALL
select '101' ID , '010-88232266' NO, '100' DI, '2' TYPE FROM DUAL
UNION ALL
select '100' ID , '12541545254' NO, '105' DI, '1' TYPE FROM DUAL
UNION ALL
select '101' ID , '028-85421142' NO, '106' DI, '2' TYPE FROM DUAL
)select A.NO CELLPHONE,B.NO CELLPHONE from A ,A B
WHERE A.ID=B.DI AND A.TYPE='1' AND B.TYPE='2'
确实
CONNECT WITH PRIOR ID=DI
select no as cellphone,
(select no from tablename b
where a.type=b.id
and a.id = b.type) as TELEPHONE
from tablename a
期望返回的记录:
CELLPHONE TELEPHONE
13902145214 010-88232266
12541545254
028-85421142
共三条记录,期盼...
期望返回的记录:
CELLPHONE TELEPHONE
13902145214 010-88232266
12541545254
____________028-85421142
共三条记录,期盼...
from
(select * from tableName where tableName.type=1) t1
outer join
(select * from tableName where tableName.type=2) t2
ON t1.ID = t2.DI and t2.ID = t1.DI
(
select '100' ID,'13902145214' NO,'101' DI,'1' TYPE FROM DUAL
UNION ALL
select '101' ID,'010-88232266' NO,'100' DI, '2' TYPE FROM DUAL
UNION ALL
select '100' ID,'12541544254' NO,'105' DI, '1' TYPE FROM DUAL
UNION ALL
select '101' ID, '028-85421142' NO,'106' DI,'2' TYPE FROM DUAL
)
select t1.NO,t2.NO from
(select * from A where A.TYPE='1') t1
full outer join
(select * from A where A.TYPE='2') t2
on t1.ID=t2.DI AND t2.ID=t1.DI