sql如下:
select distinct AA.NodeID, AA.NodeCode,AA.NodeName,AA.nodetype,AA.ModuleID,BB.LinkUrl from K_SysModuleNode AA LEFT JOIN
(select distinct M.ModuleID,M.LinkUrl,P.NodeID from K_SysModuleNode N, K_SysModule M,K_SysActionPermit A,K_SysUserGroupPermit P
WHERE M.ModuleID=A.ModuleID AND P.PermitCode=A.ID AND P.UserGroupCode IN
(SELECT K_SysUserRole.UserGroupCode FROM K_SysUserRole WHERE K_SysUserRole.UserID='5')
) BB
ON AA.NodeID=BB.NodeID WHERE AA.ModuleID=BB.ModuleID and AA.NodeCode LIKE '100%' and AA.IsValid=1 and AA.IsLeftDisplay=1 order by NodeCode现在想要的是,如果BB没有记录则返回空,不知道该怎么做
select distinct AA.NodeID, AA.NodeCode,AA.NodeName,AA.nodetype,AA.ModuleID,BB.LinkUrl from K_SysModuleNode AA LEFT JOIN
(select distinct M.ModuleID,M.LinkUrl,P.NodeID from K_SysModuleNode N, K_SysModule M,K_SysActionPermit A,K_SysUserGroupPermit P
WHERE M.ModuleID=A.ModuleID AND P.PermitCode=A.ID AND P.UserGroupCode IN
(SELECT K_SysUserRole.UserGroupCode FROM K_SysUserRole WHERE K_SysUserRole.UserID='5')
) BB
ON AA.NodeID=BB.NodeID WHERE AA.ModuleID=BB.ModuleID and AA.NodeCode LIKE '100%' and AA.IsValid=1 and AA.IsLeftDisplay=1 order by NodeCode现在想要的是,如果BB没有记录则返回空,不知道该怎么做
看看
就是这个
(select distinct M.ModuleID,M.LinkUrl,P.NodeID from K_SysModuleNode N, K_SysModule M,K_SysActionPermit A,K_SysUserGroupPermit P
WHERE M.ModuleID=A.ModuleID AND P.PermitCode=A.ID AND P.UserGroupCode IN
(SELECT K_SysUserRole.UserGroupCode FROM K_SysUserRole WHERE K_SysUserRole.UserID='5')
) BB
ON AA.NodeID=BB.NodeID WHERE AA.ModuleID=BB.ModuleID and AA.NodeCode LIKE '100%' and AA.IsValid=1 and AA.IsLeftDisplay=1 order by NodeCode
如果BB没有记录的时候 返回空(不正常,返回AA的集合)
D0F465C2-9A5C-4321-9D92-FE8EDEC37132 ../SysManage/ModelNodeList.aspx E737FE9D-857A-497C-A29C-2456314D1C65
946E2EC9-04DA-41EB-A3A1-3E5B02E7C2A0 ../SysManage/UserGropManage.aspx 01647CE0-5B30-4795-AD8D-2B69AECAEE8B
169ABDF6-75AF-43D3-B5D1-451ED79724BD ../SysManage/ModelList.aspx 1B86B4DB-36D6-41C1-A321-53E667F38DDA
252856E7-E193-4DC5-AE85-DB5D64EF29F2 ../SysManage/ActionList.aspx 78BE1D75-7F48-4ECD-9E46-BAA8A3E0EF5D
C5AA7419-C9CF-445F-A87C-BB3793C2E6ED ../SysManage/SysUserList.aspx BDE30D8C-823E-4238-A075-DA0664F43FBC
AA查询的作用是查找目录节点, 想要按权限生成目录
BB没记录表示当前用户没有权限!但是返回了aa的记录,所以错误
from
(
select distinct M.ModuleID,M.LinkUrl,P.NodeID
from K_SysModuleNode N, K_SysModule M,K_SysActionPermit A,K_SysUserGroupPermit P
WHERE M.ModuleID=A.ModuleID AND P.PermitCode=A.ID AND P.UserGroupCode IN
(
SELECT K_SysUserRole.UserGroupCode FROM K_SysUserRole WHERE K_SysUserRole.UserID='5'
)
) BB LEFT JOIN
ON AA.NodeID=BB.NodeID WHERE AA.ModuleID=BB.ModuleID and AA.NodeCode LIKE '100%' and AA.IsValid=1 and AA.IsLeftDisplay=1 order by NodeCode
select distinct AA.NodeID, AA.NodeCode,AA.NodeName,AA.nodetype,AA.ModuleID,BB.LinkUrl from K_SysModuleNode AA LEFT JOIN
(select distinct M.ModuleID,M.LinkUrl,P.NodeID from K_SysModuleNode N, K_SysModule M,K_SysActionPermit A,K_SysUserGroupPermit P
WHERE M.ModuleID=A.ModuleID AND P.PermitCode=A.ID AND P.UserGroupCode IN
(SELECT K_SysUserRole.UserGroupCode FROM K_SysUserRole WHERE K_SysUserRole.UserID='5')
) BB
ON AA.NodeID=BB.NodeID
and AA.ModuleID=BB.ModuleID
where AA.NodeCode LIKE '100%' and AA.IsValid=1 and AA.IsLeftDisplay=1 order by NodeCode
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281