select a.tdm1, a.tmc1, b.tdm2, b.tmc2 from ( select row_number() over (order by TDM1) rn, TDM1, TMC1 from TAB1 ) a, (select row_number() over (order by TDM2) rn, TDM2, TMC2 from TAB2 ) b where a.a.rn = b.rn
with TAB1 as (select 1 as TDM1, 'M1' AS TMC1 FROM DUAL UNION select 2 as TDM1, 'M2' AS TMC1 FROM DUAL UNION select 3 as TDM1, 'M3' AS TMC1 FROM DUAL), TAB2 AS (select 101 as TDM2, 'N1' AS TMC2 FROM DUAL UNION select 102 as TDM2, 'N2' AS TMC2 FROM DUAL UNION select 103 as TDM2, 'N3' AS TMC2 FROM DUAL ), TAB3 AS (select 1 as TDM1, 102 AS TDM2 FROM DUAL UNION select 2 as TDM1, 103 AS TDM2 FROM DUAL UNION select 3 as TDM1, 101 AS TDM2 FROM DUAL ) SELECT TAB1.TDM1, TAB1.TMC1, TAB3.TDM2, TAB2.TMC2 FROM TAB1, TAB2, TAB3 WHERE TAB1.TDM1 = TAB3.TDM1 AND TAB2.TDM2 = TAB3.TDM2 ORDER BY TAB1.TDM1
select TAB1.TDM1,TAB1.TMC1,TAB2.TDM2,TAB2.TMC2 from TAB1,TAB2,TAB3 where TAB1.TDM1=TAB3.TDM1 and TAB3.TDM2=TAB2.TDM2
select TAB1.TDM1,TAB1.TMC1,TAB2.TDM2,TAB2.TMC2 from TAB1,TAB2,TAB3 where TAB1.TDM1=TAB3.TDM1 and TAB3.TDM2=TAB2.TDM2
select TAB1.TDM1,TAB1.TMC1,TAB2.TDM2,TAB2.TMC2 from TAB1,TAB2,TAB3 where TAB1.TDM1=TAB3.TDM1 and TAB3.TDM2=TAB2.TDM2
select TAB1.TDM1,TAB1.TMC1,TAB2.TDM2,TAB2.TMC2 from TAB1,TAB2,TAB3 where TAB1.TDM1=TAB3.TDM1 and TAB3.TDM2=TAB2.TDM2
select TAB1.TDM1,TAB1.TMC1,TAB2.TDM2,TAB2.TMC2 from TAB1,TAB2,TAB3 where TAB1.TDM1=TAB3.TDM1 and TAB3.TDM2=TAB2.TDM2
SELECT A.TDM1, A.TMC1, B.TDM2, B.TMC2 FROM TAB1 A, TAB2 B, TAB3 C WHERE A.TDM1 = C.TDM1 AND B.TDM2 = C.TDM2 ORDER BY A.TDM1
SELECT A.TDM1, A.TMC1, B.TDM2, B.TMC2 FROM TAB1 A, TAB2 B, TAB3 C WHERE A.TDM1 = C.TDM1 AND B.TDM2 = C.TDM2 ORDER BY A.TDM1
select row_number() over (order by TDM1) rn, TDM1, TMC1 from TAB1 ) a,
(select row_number() over (order by TDM2) rn, TDM2, TMC2 from TAB2 ) b
where a.a.rn = b.rn
------------------------------
TMD1 TMC1 TDM2 TMC2
1 M1 101 N1
2 M2 102 N2
3 M3 103 N3想得到的结果是:
------------------------------
TDM1 TMC1 TDM2 TMC2
1 M1 102 N2
2 M2 103 N3
3 M3 101 N1
with TAB1 as (select 1 as TDM1, 'M1' AS TMC1 FROM DUAL UNION
select 2 as TDM1, 'M2' AS TMC1 FROM DUAL UNION
select 3 as TDM1, 'M3' AS TMC1 FROM DUAL),
TAB2 AS (select 101 as TDM2, 'N1' AS TMC2 FROM DUAL UNION
select 102 as TDM2, 'N2' AS TMC2 FROM DUAL UNION
select 103 as TDM2, 'N3' AS TMC2 FROM DUAL ),
TAB3 AS (select 1 as TDM1, 102 AS TDM2 FROM DUAL UNION
select 2 as TDM1, 103 AS TDM2 FROM DUAL UNION
select 3 as TDM1, 101 AS TDM2 FROM DUAL )
SELECT TAB1.TDM1, TAB1.TMC1, TAB3.TDM2, TAB2.TMC2
FROM TAB1, TAB2, TAB3
WHERE TAB1.TDM1 = TAB3.TDM1
AND TAB2.TDM2 = TAB3.TDM2
ORDER BY TAB1.TDM1
FROM TAB1 A, TAB2 B, TAB3 C
WHERE A.TDM1 = C.TDM1
AND B.TDM2 = C.TDM2
ORDER BY A.TDM1
FROM TAB1 A, TAB2 B, TAB3 C
WHERE A.TDM1 = C.TDM1
AND B.TDM2 = C.TDM2
ORDER BY A.TDM1