可以考虑把这三张表进行关联 尽量不要用in select .... from a, b ,c where a. =b. and b. = c. ....
xu_guanghui(小风) 你的意思是这样吗? select PrivilegeID from RolePrivilegeTable,DegreeRoleScopeTable,UserDegreeTable where (UserDegreeTable.UserID = 'test3$00000000') AND (UserDegreeTable.AppID = 'Resource' ) and (UserDegreeTable.ValidityPeriod = 0 OR UserDegreeTable.GrantDate + UserDegreeTable.ValidityPeriod >= GETDATE()) and (UserDegreeTable.Status = 'G') and (DegreeRoleScopeTable.AppID = 'Resource') AND (DegreeRoleScopeTable.NodeID = 1 OR DegreeRoleScopeTable.NodeID = 2) and (RolePrivilegeTable.AppID = 'Resource') and (RolePrivilegeTable.PrivilegeID = 'ResUpload' OR RolePrivilegeTable.PrivilegeID = 'ResDownLoad') and (RolePrivilegeTable.RoleID=DegreeRoleScopeTable.RoleID ) and (DegreeRoleScopeTable.DegreeID=UserDegreeTable.DegreeID) UNION SELECT PrivilegeID from RolePrivilegeTable,DegreeRoleScopeTable,UserGroupDegreeTable,GroupUserTable where (GroupUserTable.UserID = 'test3$00000000') AND (GroupUserTable.Status = 'G') and (UserGroupDegreeTable.ValidityPeriod = 0 OR UserGroupDegreeTable.GrantDate + UserGroupDegreeTable.ValidityPeriod >= GETDATE()) and (GroupUserTable.Status = 'G') and (UserGroupDegreeTable.AppID = 'Resource') and (DegreeRoleScopeTable.AppID = 'Resource') AND (DegreeRoleScopeTable.NodeID = 1 OR DegreeRoleScopeTable.NodeID = 2) and (RolePrivilegeTable.AppID = 'Resource') AND (RolePrivilegeTable.PrivilegeID = 'ResUpload' OR RolePrivilegeTable.PrivilegeID = 'ResDownLoad') and (UserGroupDegreeTable.NodeID=GroupUserTable.NodeID) and (DegreeRoleScopeTable.DegreeID=UserGroupDegreeTable.DegreeID) and (RolePrivilegeTable.RoleID=DegreeRoleScopeTable.RoleID)
select ....
from a, b ,c
where a. =b.
and b. = c.
....
select PrivilegeID
from RolePrivilegeTable,DegreeRoleScopeTable,UserDegreeTable
where (UserDegreeTable.UserID = 'test3$00000000') AND (UserDegreeTable.AppID = 'Resource' )
and (UserDegreeTable.ValidityPeriod = 0 OR UserDegreeTable.GrantDate + UserDegreeTable.ValidityPeriod >= GETDATE())
and (UserDegreeTable.Status = 'G') and (DegreeRoleScopeTable.AppID = 'Resource') AND (DegreeRoleScopeTable.NodeID = 1 OR DegreeRoleScopeTable.NodeID = 2)
and (RolePrivilegeTable.AppID = 'Resource') and (RolePrivilegeTable.PrivilegeID = 'ResUpload' OR RolePrivilegeTable.PrivilegeID = 'ResDownLoad')
and (RolePrivilegeTable.RoleID=DegreeRoleScopeTable.RoleID ) and (DegreeRoleScopeTable.DegreeID=UserDegreeTable.DegreeID)
UNION
SELECT PrivilegeID
from RolePrivilegeTable,DegreeRoleScopeTable,UserGroupDegreeTable,GroupUserTable
where
(GroupUserTable.UserID = 'test3$00000000') AND (GroupUserTable.Status = 'G')
and (UserGroupDegreeTable.ValidityPeriod = 0 OR UserGroupDegreeTable.GrantDate + UserGroupDegreeTable.ValidityPeriod >= GETDATE())
and (GroupUserTable.Status = 'G')
and (UserGroupDegreeTable.AppID = 'Resource')
and (DegreeRoleScopeTable.AppID = 'Resource') AND (DegreeRoleScopeTable.NodeID = 1 OR DegreeRoleScopeTable.NodeID = 2)
and (RolePrivilegeTable.AppID = 'Resource') AND (RolePrivilegeTable.PrivilegeID = 'ResUpload' OR RolePrivilegeTable.PrivilegeID = 'ResDownLoad')
and (UserGroupDegreeTable.NodeID=GroupUserTable.NodeID)
and (DegreeRoleScopeTable.DegreeID=UserGroupDegreeTable.DegreeID)
and (RolePrivilegeTable.RoleID=DegreeRoleScopeTable.RoleID)