在Oracle中联接表 [英] JOIN TABLE IN ORACLE

问题描述

我有一个包含100000条记录的表，并获得97000条记录，这是下面的查询方式，如何获取剩余的30000条记录.我尝试不等于'H.RESOURCE_ID <> SQ.CPNT_OG_ID'，但没有获得正确的数据吗?

I have a table with 100000 records and I get 97000 records as part of this below query how to get the remaining 30000 records. I tried not equal to 'H.RESOURCE_ID <> SQ.CPNT_OG_ID' but not getting correct data?

SELECT H.pernr AS "STUD_ID",
       SQ.CPNT_TYP_ID,
       SQ.CPNT_ID,
       TO_CHAR (SQ.REV_DTE, 'MON-DD-YYYY HH24:MI:SS') AS REV_DTE,
       CASE                                                           --WAIVED
          WHEN sq.CPNT_TYP_ID = 'VIDEO' AND enrollment_status = 'Complete'
          THEN
             'VIDEO-COMPLETE'
          WHEN sq.CPNT_TYP_ID = 'VIDEO' AND enrollment_status <> 'Complete'
          THEN
             'VIDEO-WAIVED'
          WHEN     sq.CPNT_TYP_ID = 'CERTIFICATION'
               AND enrollment_status = 'Complete'
          THEN
             'CERT-COMPLETE'
          WHEN     sq.CPNT_TYP_ID = 'CERTIFICATION'
               AND enrollment_status <> 'Complete'
          THEN
             'CERT-WAIVED'
          WHEN sq.CPNT_TYP_ID = 'CDOC' AND enrollment_status = 'Complete'
          THEN
             'CDOC-COMPLETE'
          WHEN sq.CPNT_TYP_ID = 'CDOC' AND enrollment_status <> 'Complete'
          THEN
             'CDOC-WAIVED'
          WHEN     sq.CPNT_TYP_ID = 'ELEARNING'
               AND enrollment_status = 'Complete'
          THEN
             'Elearning-COMPLETE-PASSED'
          WHEN     sq.CPNT_TYP_ID = 'ELEARNING'
               AND enrollment_status <> 'Complete'
          THEN
             'Elearning-WAIVED'
          ELSE
             NULL
       END
          "CMPL_STAT_ID",
       TO_CHAR (H.COMPLETION_DATE, 'MON-DD-YYYY HH24:MI:SS')
          AS COMPLETION_DATE,
       'RECORD_Admin'
          AS "ESIG_MEANING_CODE_ID",
       NULL                                           AS "LST_UPD_USR",
       sq.rev_num                                     AS "REV_NUM",
       'LEARNSHARE'                                   AS "COMMENTS"
  FROM ZB_USR_HISTORY_NEW H
       JOIN
       (SELECT DISTINCT CPNT_ID,
                        CPNT_TYP_ID,
                        CPNT_OG_ID,
                        REV_DTE,
                        REV_NUM,
                        CPNT_COURSE_NUM
          FROM TBL_ZIM_ITEM_new
         WHERE VALID = '1' AND app_id LIKE '%en-us%' OR LENGTH (app_id) < 9)
       SQ
          ON (H.RESOURCE_ID = SQ.CPNT_OG_ID);

推荐答案

只需用LEFT JOIN替换JOIN.您还可以在SQL中使用LEFT JOIN或RIGHT JOIN以便还获取不满足联接条件的记录.在不存在的行中选择的列将为NULL.

Just replace JOIN with LEFT JOIN. You can use LEFT JOIN or RIGHT JOIN in SQL in order to also fetch the record that do not satisfy the join condition. The columns selected in the non-existing row will be NULL.

这篇关于在Oracle中联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！