由网友(￡狂乱メ虐杀╮)分享简介：我有两个表，它们的列除了1以外都相同。因此，例如：Table1 (column names): Student | Course | Exam1 | Exam2Table2 (column names): Student | Course | Exam3 | FinalExam我想合并这两个表以获得：Table: S...

我有两个表，它们的列除了1以外都相同。因此，例如：

Table1 (column names): Student | Course | Exam1 | Exam2
Table2 (column names): Student | Course | Exam3 | FinalExam

我想合并这两个表以获得：

Table: Student | Course | Exam1 | Exam2 | FinalExam

我有以下内容：

Select
    student,
    course,
    Exam1, 
    Exam2
From Table1

Select
    student,
    course,
    Exam3, 
    FinalExam
From Table2


Select
    student,
    course,
    Coalesce( t1.Exam1, 0) as Exam1 
    Coalesce( t1.Exam2, 0) as Exam2
    Coalesce( t2.Exam3, 0) as Exam3
    Coalesce( t2.FinalExam, 0) as FinalExam
From Table1 t1, Table2 t2

是否有使用内部联接更高效/更简洁地执行此操作的方法？

推荐答案

您所做的是n*n行的笛卡尔积。

试试

Select
    student,
    course,
    Exam1,
    Exam2,
    Exam3,
    FinalExam,
From Table1 t1, Table2 t2
Where t1.student=t2.student
and t1.course=t2.course;

此查询的依据是学生至少参加了考试1或考试2以及考试3或期末考试。 如果可能存在缺席的情况，则需要使用外部联接。 类似于以下示例，但不限于

Select
    student,
    course,
    Exam1,
    Exam2,
    Exam3,
    FinalExam,
From Table1 t1, Table2 t2
Where t1.student=t2.student(+)
and t1.course=t2.course(+);