MAXIMO查询菜单并根据权限过滤

最新推荐文章于 2019-08-26 15:26:28 发布

原创最新推荐文章于 2019-08-26 15:26:28 发布 · 325 阅读

0 ·

CC 4.0 BY-SA版权

MAXIMO 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种复杂的SQL查询方法，用于获取不同层级的菜单项及其相关信息。通过联接多个表并使用子查询来筛选出特定用户可见的菜单项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询一级菜单
select t.position ID,
ELEMENTTYPE appType,
mod.description,
NULL PID
from maxmenu t, maxmodules mod
where t.keyvalue = mod.module
and t.menutype = 'MODULE'
and t.elementtype = 'MODULE'
and t.visible = 1
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.elementtype = 'APP'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ')) ORDER BY position

查询二级菜单，一级菜单为300000
SELECT * FROM (
(select t.position ID,
ELEMENTTYPE appType,
t.headerdescription description,
'' apptable,
m.position PID
from maxmenu t,
(select t.position, mod.module
from maxmenu t, maxmodules mod
where t.keyvalue = mod.module
and t.menutype = 'MODULE'
and t.elementtype = 'MODULE'
and t.visible = 1
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.elementtype = 'APP'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))) m,
(select POSITION,MODULEAPP,COUNT(1) CONUNTNUM from maxmenu m2,maxapps app where m2.keyvalue=app.app and m2.Elementtype='APP'
and M2.keyvalue in
(select m.keyvalue
from maxmenu m
where m.menutype = 'MODULE'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))
GROUP BY POSITION,MODULEAPP HAVING COUNT(1)>0) CON
where t.menutype = 'MODULE'
and t.elementtype = 'HEADER'
and t.moduleapp = m.module
AND t.position=CON.POSITION
AND T.moduleapp=CON.moduleapp
and t.visible = 1
and m.position='300000'
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ')))

union all
(select t.position ID,
ELEMENTTYPE appType,
t.headerdescription description,
p.maintbname apptable,
m.position PID
from maxmenu t,
maxapps p,
(select t.position, mod.module
from maxmenu t, maxmodules mod
where t.keyvalue = mod.module
and t.menutype = 'MODULE'
and t.elementtype = 'MODULE'
and t.visible = 1
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.elementtype = 'APP'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))) m
where t.keyvalue = p.app
and t.menutype = 'MODULE'
and t.elementtype = 'APP'
and t.moduleapp = m.module
and t.subposition = 0
and t.visible = 1
and m.position='200000'
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))))
ORDER BY ID

查询三级菜单，二级菜单为304000
select t.position ID,
ELEMENTTYPE appType,
p.description description,
p.maintbname apptable,
pa.position PID
from maxmenu t,
maxapps p,
(select t.position, M.module
from maxmenu t,
(select t.position, mod.module
from maxmenu t, maxmodules mod
where t.keyvalue = mod.module
and t.menutype = 'MODULE'
and t.elementtype = 'MODULE'
and t.visible = 1
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.elementtype = 'APP'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))) m
where t.menutype = 'MODULE'
and t.elementtype = 'HEADER'
and t.moduleapp = m.module
and t.visible = 1
and t.moduleapp in
(select m.moduleapp
from maxmenu m
where m.menutype = 'MODULE'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))) PA
where t.keyvalue = p.app
and t.menutype = 'MODULE'
and t.elementtype = 'APP'
and t.moduleapp = PA.module
AND T.position = PA.position
and t.visible = 1
and PA.position='304000'
and t.keyvalue in
(select m.keyvalue
from maxmenu m
where m.menutype = 'MODULE'
and m.keyvalue in
(select distinct a.app
from applicationauth a
where a.groupname in
(select g.groupname
from groupuser g
where g.userid = 'ADMIN')
and a.optionname = 'READ'))