在两段SQL语句中间加NOT EXISTS语句时,无法查询获得结果-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

在两段SQL语句中间加NOT EXISTS语句时,无法查询获得结果

吴孟桥 2016-06-08 16:41:50 1214

现在遇到一个问题,始终不知道如何解决。
要写的SQL语句的功能是取出某几张表中的数据不含指定表中的某几行的数据;
首先我的取出整体的记录SQL语句如下:
screenshot

查询结果集如下:
screenshot

查询该集中的部分集SQL代码如下:
screenshot

执行结果如下:
planMaker

为何我在两段SQL语句中间加NOT EXISTS语句时,就无法查询获得结果?
合起来的SQL语句如下:

screenshot

SQL
分享到
取消 提交回答
全部回答(1)
  • 吴孟桥
    2019-07-17 19:32:18

    not EXISTS 通俗点讲,就是返回一个boolean类型 。也就是你的这个条件的意思,如果

    SELECT
        militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId
    FROM
        militarySuperviseQuarterSubmissionTable,
        projectMemberRelationTable
    WHERE
        militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId = projectMemberRelationTable.systemUserID
    AND projectMemberRelationTable.projectID = 22
    AND militarySuperviseQuarterSubmissionTable.militarySuperVisorUniqueId = 2

    这里有查询出来没有数据,条件才成立

    废话不多说,你想要的结果应该是这样的吧:

        SELECT
        BSPMSuser.userName,
        BSPMSuser.userDepartment,
        projectMemberRelationTable.systemUserID,
        userAuthority.authorityName,
        BSPMSuser.MemberResponsiblity,
        BSPMSuser.memberPosition,
        BSPMSuser.MemberTitle,
        BSPMSuser.memberAddress,
        BSPMSuser.userPhoneNumber,
        BSPMSuser.memberNote
    FROM
        BSPMSuser,
        userAuthority,
        militarySuperviseQuarterSubmissionTable,
        projectMemberRelationTable
    WHERE
        projectMemberRelationTable.projectID = 22
    AND projectMemberRelationTable.authorityId = userAuthority.userGroupID
    AND projectMemberRelationTable.systemUserID = BSPMSuser.userID
    AND NOT IN (
        SELECT
            militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId
        FROM
            militarySuperviseQuarterSubmissionTable,
            projectMemberRelationTable
        WHERE
            militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId = projectMemberRelationTable.systemUserID
        AND projectMemberRelationTable.projectID = 22
        AND militarySuperviseQuarterSubmissionTable.militarySuperVisorUniqueId = 2
    );
    0 0
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程