SQL（Oracle）-选择记录不存在的地方或第二张表中的另一列为真

2024-04-29 • 问答

      Applications-Table  
    ---------------------
ID          FIRST_NAME          LAST_NAME          APPLICATION_TYPE          STATUS_TYPE
123         JOHN                SMITH              EDUCATION                 APPROVED
456         JANE                DOE                EDUCATION                 APPROVED
789         SARA                JANE               EDUCATION                 APPROVED
321         BOB                 KANE               GENERAL                   APPROVED
879         OLIVER              QUEEN              GENERAL                   APPROVED

     Assignments-Table
ID          APPLICATION_ID         INactIVE
0001        456                    0
0002        789                    1

我正在尝试编写一条类似于上面示例的sql语句。我需要的结果是存在于应用程序表中的记录，该记录具有教育的应用程序类型和已批准的状态类型，但在分配表中没有记录。但是，如果分配表中存在记录，则“非活动”列必须为1。以下是我一直在使用的

SELECT 
    application.ID,application.FIRST_NAME,application.LAST_NAME,FROM applications application
LEFT JOIN assignments assignment ON application.ID = assignment.APPLICATION_ID
WHERE application.STATUS_TYPE = 'APPROVED' 
AND application.APPLICATION_TYPE = 'EDUCATION'
AND assignment.APPLICATION_ID IS NULL OR assignment.INactIVE != 0

我也尝试了下面的sql，但随后得到了John Smith，Jane Doe和Sara Jane。

    SELECT 
        application.ID,FROM applications application
    LEFT JOIN assignments assignment ON application.ID = 
    assignment.APPLICATION_ID
WHERE application.STATUS_TYPE = 'APPROVED' 
AND application.APPLICATION_TYPE = 'EDUCATION'
AND (assignment.APPLICATION_ID IS NULL OR assignment.INactIVE != 0)

我希望能得到的记录是约翰·史密斯和萨拉·简。目前，我正在结果集中获得这2条记录和我不想要的Jane Doe。

我在想not exists：

select a.*
from applications a
where a.STATUS_TYPE = 'APPROVED' and
      a.APPLICATION_TYPE = 'EDUCATION' and
      not exists (select 1
                  from assignments am
                  where a.ID = am.APPLICATION_ID and am.inactive <> 1
                 );

如果您想使用left join来表达这一点，则逻辑是：

select a.*
from applications a left join
     assignments am
     on a.ID = am.APPLICATION_ID and am.inactive <> 1
where a.STATUS_TYPE = 'APPROVED' and
      a.APPLICATION_TYPE = 'EDUCATION' and
      am.application_id is null;

Here是db 小提琴。

您不应该在where条件下使用左联接表列（否则，这将作为内部联接）

SELECT 
    application.ID,application.FIRST_NAME,application.LAST_NAME,FROM applications application
LEFT JOIN assignments assignment ON application.ID = assignment.APPLICATION_ID
 AND ( assignment.APPLICATION_ID IS NULL OR assignment.INACTIVE != 0)
WHERE application.STATUS_TYPE = 'APPROVED' 
AND application.APPLICATION_TYPE = 'EDUCATION'
AND assignment.APPLICATION_ID is null

您应该将它们移到相关的ON子句中，并只允许空值的条件进入（不在）

您可以尝试以下操作：

SELECT appl.id,appl.first_name,appl.last_name
FROM   applications appl
WHERE  appl.application_type = 'EDUCATION'
  AND  appl.status_type = 'APPROVED'
  AND  NOT EXISTS (SELECT *
                   FROM   assignments asgn
                   WHERE  appl.id = asgn.application_id
                     AND  asgn.inactive = 0)

SQL（Oracle）-选择记录不存在的地方或第二张表中的另一列为真

napkinnn 回答：SQL（Oracle）-选择记录不存在的地方或第二张表中的另一列为真

大家都在问