用可能不存在的记录连接表

Question

下面的表格让我有点头疼。我想要做的是创建一个查询，返回给定课程的所有作业，并提供作业信息(成绩、平均成绩等)。或者，如果学生没有交作业或者没有交作业，那么这些字段就是空的。我试过了left join，但是我想不出那些桌子是什么样子的。它们是旧的，结构很差，但这是我的全部。

CourseInst_Students在课程实例中为学生进行注册。

| ID_CourseInst| SSN                 |
| =============|=====================|
| 24744        | 080590-XXXX         | my ssn

Assignments_CourseInst注册到课程实例的分配

| ID_Assignment| ID_CourseInst       |
| =============|=====================|
| 37978        | 24744               |
| 37979        | 24744               |
| 37992        | 24744               |
| 38046        | 24744               |

赋值

 | ID_Assignment| Title                     |
 | =============|===========================|
 | 37978        | Og þá var kátt í höllinni |
 | 37979        | Test for Assignments      |
 | 37992        | Test 2                    |
 | 38046        | Eitthvað gott verkefni    |

Assignments_Solutions持有一份信息(等级、交单日期等)对一群学生来说(1比多)。如果学生没有提交他们的解决方案，就没有记录。

 | ID_Assignment| ID_Group                  | Grade   |
 | =============|===========================|=========|
 | 37978        | 808046                    | 10      | only one group has handed in.

Assignments_Solutions_Groups将学生的SSN注册到一个组。

| ID_Group     | SSN                       |
| =============|===========================|
| 808046       | 221180-XXXX               | not my SSN (see mine above)

在为SSN搜索任务时，我喜欢的结果如下：

| ID_Assignment| Title                     | Grade   |
| =============|===========================|=========|
| 37978        | Og þá var kátt í höllinni | NULL    |
| 37979        | Test for Assignments      | NULL    |
| 37992        | Test 2                    | NULL    |
| 38046        | Eitthvað gott verkefni    | NULL    |

有人能搞清楚吗？谢谢!

更新

这是我到目前为止所得到的

select A.ID_Assignment, A.Title, ASo.Grade
from CourseInst_Students as CS
join Assignments_CourseInst as AC on CS.ID_CourseInst= AC.ID_CourseInst
join Assignments as A on AC.ID_Assignment = A.ID_Assignment
left join Assignments_Solutions as ASo on A.ID_Assignment = ASo.ID_Assignment
left join Assignments_Solutions_Groups as ASGs on ASo.ID_Group = ASGs.ID_Group
where CS.SSN = '080590-XXXX'
      AND CS.id_namskeid = 24744

我得到了以下结果：

| ID_Assignment| Title                     | Grade   |
| =============|===========================|=========|
| 37978        | Og þá var kátt í höllinni | 10      | <- this is someone else's handin. I want NULL here
| 37979        | Test for Assignments      | NULL    | 
| 37992        | Test 2                    | NULL    |
| 38046        | Eitthvað gott verkefni    | NULL    |

Answer 1

假设是Oracle，问题在于设计(绝对是垃圾)。如果一个学生没有入学，他就不能提交。在您的情况下，学生1已注册，但学生2已提交。您将最终在笛卡儿产品，以达到预期的结果。请设计这个程序的人阅读SQL的基本知识。

WITH C_S
    AS (SELECT
             '24744' AS ID_COURSEINST,
             '080590-XXXX' AS SSN
        FROM
             DUAL
        UNION ALL
        SELECT
             '24744' AS ID_COURSEINST,
             '221180-XXXX' AS SSN
        FROM
             DUAL),
    A_C
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '37979' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '37992' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '38046' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL),
    ASSI
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             'Og þá var kátt í höllinni' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '37979' AS ID_ASSIGNMENT,
             'Test for Assignments' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '37992' AS ID_ASSIGNMENT,
             'Test 2' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '38046' AS ID_ASSIGNMENT,
             'Eitthvað gott verkefni' AS TITLE
        FROM
             DUAL),
    A_S
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             '808046' AS ID_GROUP,
             '10' AS GRADE
        FROM
             DUAL),
    A_S_G
    AS (SELECT
             '808046' AS ID_GROUP,
             '221180-XXXX' AS SSN
        FROM
             DUAL),
    GRP_SUBMIT
    AS (SELECT
             A_S.ID_ASSIGNMENT ID_ASSIGNMENT_AS,
             A_S.ID_GROUP ID_GROUP_AS,
             A_S.GRADE,
             C_S.ID_COURSEINST COURSE_SUB,
             C_S.SSN SUBMIT_SSN
        FROM
             C_S,
             A_S,
             A_S_G
        WHERE
             A_S_G.SSN = C_S.SSN
             AND A_S_G.ID_GROUP = A_S.ID_GROUP),
    GRP_ASSIGN
    AS (SELECT
             A_C.ID_ASSIGNMENT ID_ASSIGNMENT_AC,
             A_C.ID_COURSEINST AS ID_COURSEINST_AC,
             ASSI.ID_ASSIGNMENT ID_ASSIGNMENT_ASSI,
             ASSI.TITLE TITLE_ASSI,
             C_S.ID_COURSEINST COURSE_ASSI,
             C_S.SSN ASSI_SSN
        FROM
             A_C,
             ASSI,
             C_S
        WHERE
             A_C.ID_ASSIGNMENT = ASSI.ID_ASSIGNMENT)
SELECT
      ID_ASSIGNMENT_ASSI,
      TITLE_ASSI,
      GRADE
FROM
      (SELECT
            *
       FROM
                GRP_SUBMIT
            RIGHT OUTER JOIN
                GRP_ASSIGN
            ON ID_ASSIGNMENT_AS = ID_ASSIGNMENT_AC
               AND SUBMIT_SSN = ASSI_SSN)
WHERE
      ASSI_SSN = '221180-XXXX'
      AND COURSE_ASSI = 24744
ORDER BY
      ID_ASSIGNMENT_ASSI;

在GRP_SUBMIT中，我检查提交作业的学生数量。

在GRP_ASSIGN中，我操纵笛卡尔积的预期提交数。

在最后的选择中，我让GRP_SUBMIT在子查询中在GRP_ASSIGN上有一个正确的外部连接，然后根据搜索进行过滤。

结果：

输入: 221180-XXXX

ID_ASSIGNMENT_ASSI TITLE_ASSI                GRADE
------------------ ------------------------- -----
37978              Og þá var kátt í höllinni 10   
37979              Test for Assignments           
37992              Test 2                         
38046              Eitthvað gott verkefni         

4 rows selected.

输入: 080590-XXXX

ID_ASSIGNMENT_ASSI TITLE_ASSI                GRADE
------------------ ------------------------- -----
37978              Og þá var kátt í höllinni    
37979              Test for Assignments           
37992              Test 2                         
38046              Eitthvað gott verkefni         

4 rows selected.

Answer 2

您能分享到目前为止您尝试过的查询吗？另外，您是否使用MSSQL、MySQL、.？

你试过这样的东西吗？

select a.ID_Assignment, a.Title, asol.Grade from Assignments a
        left join Assignments_Solutions asol 
        on asol.ID_Assigment = a.ID_Assignment
        left join Assignments_Solutions_Groups asg
        on asg.ID_Group = asol.ID_Group
        where asg.SSN = 'yourSocialSecurityNumber';