学习开发不久,现在遇到一个问题,始终不知道如何解决。
要写的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)