本文发表在 rolia.net 枫下论坛In Oracle Database, how to get the results, please see descriptions below:

Table: CourseStudent
has columns: Course, StudentName (with values below)
--------------------------------
Course ---- StudentName
--------------------------------
C1----------S1
C1----------S2
C1----------S3
C1----------S4
C2----------S1
C2----------S3
C3----------S5
C3----------S6
. ---------- .
. ---------- .
. ---------- .

Another Table: NewStudents
-- we have some new students below:
---------------------
column: StudentName:
---------------------
SA
SB
SC
.
.
.


And we want all these new students to join all the courses above:
So, the resultset will be:
-- **************************
-- Target Resultset
-- **************************

--------------------------------
Course ---- StudentName
--------------------------------
C1----------S1
C1----------S2
C1----------S3
C1----------S4
C1----------SA
C1----------SB
C1----------SC
. ---------- .
. ---------- .
. ---------- .
C2----------S1
C2----------S3
C2----------SA
C2----------SB
C2----------SC
. ---------- .
. ---------- .
. ---------- .
C3----------S5
C3----------S6
C3----------SA
C3----------SB
C3----------SC
. ---------- .
. ---------- .
. ---------- .


Now each course has all new students joined.




Question: Is there one SQL to achieve the above target resultset? Otherwise, what will be shortest steps to achieve it?
Thanks.



-- ----------------------------------------------------------------
-- You can use the following statements for quick sample records in Oracle
-- ----------------------------------------------------------------
Drop Table CourseStudent;
Create Table CourseStudent (Course Varchar2(50),StudentName Varchar2(50));
-- --
Insert Into CourseStudent (Course,StudentName) Values ('C1','S1');
Insert Into CourseStudent (Course,StudentName) Values ('C1','S2');
Insert Into CourseStudent (Course,StudentName) Values ('C1','S3');
Insert Into CourseStudent (Course,StudentName) Values ('C1','S4');
Insert Into CourseStudent (Course,StudentName) Values ('C2','S1');
Insert Into CourseStudent (Course,StudentName) Values ('C2','S3');
Insert Into CourseStudent (Course,StudentName) Values ('C3','S5');
Insert Into CourseStudent (Course,StudentName) Values ('C3','S6');
Commit;
-- --------------------------------
Drop Table NewStudents;
Create Table NewStudents (StudentName Varchar2(50));
-- --
Insert Into NewStudents (StudentName) Values ('SA');
Insert Into NewStudents (StudentName) Values ('SB');
Insert Into NewStudents (StudentName) Values ('SC');
Commit;
-- --------------------------------更多精彩文章及讨论，请光临枫下论坛 rolia.net