2 u010290146 u010290146 于 2014.04.08 11:14 提问

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

学习开发不久,现在遇到一个问题,始终不知道如何解决。
要写的SQL语句的功能是取出某几张表中的数据不含指定表中的某几行的数据;
首先我的取出整体的记录SQL语句如下:
select BSPMSuser.userName,BSPMSuser.userDepartment,BSPMSuser.userID,
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
查询结果集如下:
系统管理员 设计部 admin 系统管理员 NULL NULL NULL NULL
计划制定者 指挥部 planMaker 计划制定组 NULL NULL NULL NULL
计划跟踪者 运输部 planTracer 普通用户组 NULL NULL NULL NULL
计划审批者 设计部 planApproval 计划审批组 NULL NULL NULL NULL
查询该集中的部分集SQL代码如下:
select militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId from
militarySuperviseQuarterSubmissionTable,projectMemberRelationTable
where militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId=projectMemberRelationTable.systemUserID
and projectMemberRelationTable.projectID= 22 and
militarySuperviseQuarterSubmissionTable.militarySuperVisorUniqueId= 2
执行结果如下:
planMaker

为何我在两段SQL语句中间加NOT EXISTS语句时,就无法查询获得结果?
合起来的SQL语句如下:
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 exists
(select militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId from
militarySuperviseQuarterSubmissionTable,projectMemberRelationTable
where militarySuperviseQuarterSubmissionTable.fileSubmissionPersonId=projectMemberRelationTable.systemUserID
and projectMemberRelationTable.projectID= 22 and
militarySuperviseQuarterSubmissionTable.militarySuperVisorUniqueId= 2)

1个回答

shendixiong
shendixiong   2014.04.10 10:22

有几个问题要说:
第一:你的SQL排版要清晰点啦
第二: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
);
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!