sql - SQL 显示每个类

Question

有人可以帮我处理我的 Oracle SQL 连接吗，有些东西不见了。下面的选择语句列出了每个学生和上课日期，但我想显示每个学生的每个班级，即使学生是否参加了课程。Kim，Brandy 和 Trina，Brandy 应该列出每个班级名称，没有上课日期，因为他们还没有上课。格林，罗伯特上过 3 节课，没有上过 2 节课，这 2 节课也应该列出，没有上课日期，因为他没有上过。列出了插入语句和创建表。如果您有任何问题，请告诉我。谢谢

SELECT VW.STUDENT_NAME,
       VW.EMPLE_NO,
       CN.PK_CLASS_NAME_ID,
       CN.CLASS_NAME,
       DP.CLASS_DATE
  FROM EMPLOYEE_NAME  VW
       LEFT JOIN DEMO_PRODUCT_INFO_NEW DP ON DP.FK_CO_EMPL_ID = VW.EMPLE_NO
       LEFT JOIN TBL_CLASS_NAME_NEW CN
           ON CN.PK_CLASS_NAME_ID = DP.FK_CLASS_NAME_ID
ORDER BY STUDENT_NAME ASC;

CREATE TABLE TBL_CLASS_NAME_NEW
(
  PK_CLASS_NAME_ID  INTEGER,
  CLASS_NAME        VARCHAR2(75 BYTE)
);

Insert into TBL_CLASS_NAME_NEW
   (PK_CLASS_NAME_ID, CLASS_NAME)
 Values
   (1, 'CPR');
Insert into TBL_CLASS_NAME_NEW
   (PK_CLASS_NAME_ID, CLASS_NAME)
 Values
   (3, 'ETHICS');
Insert into TBL_CLASS_NAME_NEW
   (PK_CLASS_NAME_ID, CLASS_NAME)
 Values
   (4, 'HARRASEMENT');
Insert into TBL_CLASS_NAME_NEW
   (PK_CLASS_NAME_ID, CLASS_NAME)
 Values
   (5, 'DEFENSIVE TEST');
Insert into TBL_CLASS_NAME_NEW
   (PK_CLASS_NAME_ID, CLASS_NAME)
 Values
   (2, 'RANGE');
COMMIT;



CREATE TABLE DEMO_PRODUCT_INFO_NEW
(
  PRODUCT_ID           NUMBER                   NOT NULL,
  FK_CO_EMPL_ID        NUMBER,
  FK_CLASS_NAME_ID     NUMBER,
  CLASS_DATE           DATE
);


Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (4, 4, 1, TO_DATE('7/18/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (22, 4, 1, TO_DATE('7/25/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (18, 4, 4, TO_DATE('7/18/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (21, 4, 3, TO_DATE('7/4/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (32, 22, 2, TO_DATE('8/15/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (34, 22, 1, TO_DATE('8/29/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (35, 22, 1, TO_DATE('7/4/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (46, 18, 4, TO_DATE('7/4/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (43, 18, 5, TO_DATE('7/11/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (45, 4, 2, TO_DATE('7/4/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (48, 4, 5, TO_DATE('7/11/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (51, 22, 3, TO_DATE('7/4/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (52, 18, 3, TO_DATE('7/18/2021', 'MM/DD/YYYY'));
Insert into DEMO_PRODUCT_INFO_NEW
   (PRODUCT_ID, FK_CO_EMPL_ID, FK_CLASS_NAME_ID, CLASS_DATE)
 Values
   (5, 4, 2, TO_DATE('7/25/2021', 'MM/DD/YYYY'));
COMMIT;

CREATE TABLE EMPLOYEE_NAME
(
    EMPLE_NO        INTEGER,
    STUDENT_NAME    VARCHAR2 (100 BYTE),
    LAST_NAME       VARCHAR2 (40 BYTE),
    FIRST_NAME      VARCHAR2 (40 BYTE)
);

Insert into EMPLOYEE_NAME
   (EMPLE_NO, STUDENT_NAME, LAST_NAME, FIRST_NAME)
 Values
   (4, 'WENDY, FRANK', 'FRANK', 'WENDY');
Insert into EMPLOYEE_NAME
   (EMPLE_NO, STUDENT_NAME, LAST_NAME, FIRST_NAME)
 Values
   (22, 'JOHN, JAMES', 'JAMES', 'JOHN');
Insert into EMPLOYEE_NAME
   (EMPLE_NO, STUDENT_NAME, LAST_NAME, FIRST_NAME)
 Values
   (18, 'GREEN, ROBERT', 'ROBERT', 'GREEN');
Insert into EMPLOYEE_NAME
   (EMPLE_NO, STUDENT_NAME, LAST_NAME, FIRST_NAME)
 Values
   (21, 'KIM, BRANDY', 'BRANDY', 'KIM');
Insert into EMPLOYEE_NAME
   (EMPLE_NO, STUDENT_NAME, LAST_NAME, FIRST_NAME)
 Values
   (32, 'TRINA, JAMIE', 'JAMIE', 'TRINA');

标签： sqloracletoad

Answer 1

这就是以下查询的工作方式

T1：列出学生可以选择的所有课程和学生。

T2：列出学生选择的所有课程和学生

然后T1去T2 LEFT JOIN与相等数量PK_CLASS_NAME_ID和EMPLE_NO

SELECT 
    T1.STUDENT_NAME,
    T1.EMPLE_NO,
    T1.PK_CLASS_NAME_ID,
    T1.CLASS_NAME,
    T2.CLASS_DATE
FROM 
(
    SELECT VW.STUDENT_NAME,
           VW.EMPLE_NO,
           CN.PK_CLASS_NAME_ID,
           CN.CLASS_NAME 
    FROM TBL_CLASS_NAME_NEW CN 
      JOIN EMPLOYEE_NAME VW 
         ON 1=1
     GROUP BY VW.STUDENT_NAME,VW.EMPLE_NO, CN.PK_CLASS_NAME_ID, CN.CLASS_NAME 
) T1

LEFT JOIN

(
  
   SELECT  VW.STUDENT_NAME,
           VW.EMPLE_NO,
           CN.PK_CLASS_NAME_ID,
           CN.CLASS_NAME,
           DP.CLASS_DATE
   FROM TBL_CLASS_NAME_NEW CN 
     JOIN DEMO_PRODUCT_INFO_NEW DP
           ON CN.PK_CLASS_NAME_ID = DP.FK_CLASS_NAME_ID
     JOIN EMPLOYEE_NAME VW 
           ON DP.FK_CO_EMPL_ID = VW.EMPLE_NO
    GROUP BY  VW.STUDENT_NAME,VW.EMPLE_NO,CN.PK_CLASS_NAME_ID, CN.CLASS_NAME,DP.CLASS_DATE
) T2 ON T1.EMPLE_NO = T2.EMPLE_NO ANd T1.PK_CLASS_NAME_ID = T2.PK_CLASS_NAME_ID

我在 SQL 中得到了输出，但没关系，它在 Oracle 中工作。