ora SQL 一些连接的奇怪内容

表连接

问题：有一张学生表，一张老师表，现在想要把两张表关联起来，生成班级表

具体问题抽象化：

• 表1
  
• 表2
  
• 最终想要结果

做法

• 思路描述

增加辅助列，进行关联

• 做法展示

WITH TEMP1 AS(  -- 建立Teacher 表，N1为老师名称，N2为班级名称
SELECT 1 AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 2 AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 3 AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 4 AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 5 AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 6 AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 7 AS N1,'B' AS N2 FROM DUAL
)
,TEMP2 AS( -- 建立Student表，N1为学生名称，N2为班级名称
SELECT 'M' AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 'N' AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 'O' AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 'P' AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 'Q' AS N1,'A' AS N2 FROM DUAL
UNION ALL 
SELECT 'R' AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 'S' AS N1,'B' AS N2 FROM DUAL
UNION ALL 
SELECT 'T' AS N1,'B' AS N2 FROM DUAL
)
,TEMP3 AS(   --增加辅助列  以便进行精确匹配
SELECT CONCAT(row_number() over(partition by T1.N2 ORDER BY T1.N2 ),T1.N2) AS RN,T1.N2,T1.N1
FROM TEMP1 T1
)
,TEMP4 AS(  --增加辅助列  以便进行精确匹配
SELECT CONCAT(row_number() over(partition by T2.N2 ORDER BY T2.N2 ),T2.N2) AS RN1,T2.N2,T2.N1
FROM TEMP2 T2
)
SELECT NVL(t3.n2,t4.n2) AS n2,T3.N1,T4.N1 AS N1_4
FROM TEMP3 T3         
FULL JOIN TEMP4 T4 ON T3.RN=T4.RN1        -- 通过 Full join 进行全连接