sql - 按预查询记录中的属性查询（Oracle）

Question

有一个如下表MEETINGPARTICIPANT，PK 是ORGID，MEETINGID并且PARTICIPANTID

Name               Null?    Type          
------------------ -------- ------------- 
ORGID              NOT NULL NUMBER(10)    
MEETINGID          NOT NULL VARCHAR2(32) 
PARTICIPANTID      NOT NULL VARCHAR2(32) 
EMPLOYEEID         NOT NULL VARCHAR2(32) 
DISPLAYNAME                 VARCHAR2(128) 
EMAIL                       VARCHAR2(128) 
JOINTIME           NOT NULL DATE          
LEAVETIME          NOT NULL DATE          
CREATETIME         NOT NULL DATE          
LASTMODIFIEDTIME   NOT NULL DATE 

第 1 步，按和列出记录ORGID，得到一个记录列表。MEETINGIDEMPLOYEEID

SELECT
    *
FROM
    MEETINGPARTICIPANT
WHERE
    ORGID=#{orgId}
    AND MEETINGID=#{meetingId}
    AND EMPLOYEEID=#{employeeId}

步骤2，迭代步骤1的每条记录，再按步骤1的and搜索EMAIL，DISPLAYNAME合并成一个大的记录列表

for (each record in step 1) {

    SELECT
        *
    FROM
        MEETINGPARTICIPANT
    WHERE
        ORGID=#{orgId}
        AND MEETINGID=#{meetingId}
        AND EMAIL=#{record.email} AND DISPLAYNAME=#{record.displayName}

    Merge searched records to mergedRecords
}

第三步，返回mergedRecords

问题是，如何将步骤 1 和 2 中的 SQL 合并为一个 SQL？

标签： sqloraclenested-query

Answer 1

看来下面的 SQL 有效。NVL(EMAIL, ' ')and将NVL(DISPLAYNAME, ' ')处理 EMAIL 或 DISPLAYNAME 为空的记录。

SELECT
    *
FROM
    MEETINGPARTICIPANT
WHERE
    ORGID=#{orgId}
    AND MEETINGID=#{meetingId}
    AND (NVL(EMAIL, ' '), NVL(DISPLAYNAME, ' ')) IN (
        SELECT
            NVL(EMAIL, ' '), NVL(DISPLAYNAME, ' ')
        FROM
            MEETINGPARTICIPANT
        WHERE
            ORGID=#{orgId}
            AND MEETINGID=#{meetingId}
            AND EMPLOYEEID=#{employeeId}
    )