×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Oracle SQL Question

本文发表在 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
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / Oracle SQL Question
    本文发表在 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
    • cannot you do something like insert into CourseStudent select Course .Course,NewStudents .StudentName from NewStudents ,Course ?
      • Thanks for quick reply. Would you mind give a sql? Not quite get your idea. Thanks anyway.
        • isn't "insert into CourseStudent select Course .Course,NewStudents .StudentName from NewStudents ,Course" a SQL ?
          • Great! You're right, it is the answer. I sort of transformed it a little bit. Thanks a lot. :)
            Select Course, StudentName From CourseStudent
            Union
            select Distinct CourseStudent.Course,NewStudents.StudentName
            From NewStudents ,CourseStudent
            Order by 1,2
            ;
            • 这么说你没有course表?那么就用子查询做一张。你的第二个子查询有可能有效率问题,因为旧表可能较大,你用CROSS JOIN会产生巨大数字。改进如下:
              Select Course, StudentName From CourseStudent
              Union
              select course.Course,NewStudents.StudentName
              From NewStudents ,(SELECT DISTINCT course FROM coursestudent) course
              Order by 1,2
              ;
              • Now it's perfect! Thanks a lot! :)