Home >Backend Development >PHP Tutorial >MSQL数据库多表查询有关问题

MSQL数据库多表查询有关问题

WBOY
WBOYOriginal
2016-06-13 10:22:39839browse

MSQL数据库多表查询问题
时这样的,我要做权限管理,要分模块,但是这个要多表查询,我在数据库写了好久都没写出查询语句出来,请大侠帮忙
=================================================================================================================
现有5张表,
用户表sub(id,name,pass,role_id)role_id为角色id
角色表role_table(role_id,name)
模块表module(md_id,md_name)md_id为模块名称
功能表auy(auy_id,md_id,auy_name,auy_url)auy_id为功能id,auy_url为功能路径
角色功能关联表role_auy(role_id,auy_id)
=================================================================================================================
现在知道角色id,通过角色id查找相应的功能,然后显示着相应的模块下面,如果直接把所有的功能一次查询出来,我就已经弄好了,但是经理让我把功能放在相应的模块下面,希望各位大侠,告诉我怎样查询的思路,让我不再纠结,谢谢大家

------解决方案--------------------
也不贴结果,试试下面。

SQL code
select a.role_id,a.auy_id,a.auy_name,m.md_name from role_auy as ra left join auy as a on a.auy_id=ra.auy_id inner left join module as m on m.md_id=a.md_id where ra.role_id=1;<br><font color="#e78608">------解决方案--------------------</font><br><br><br>
SQL code
SELECT * FROM role_auy AS ra LEFT JOIN auy AS a ON ra.auy_id = a.auy_id LEFT JOIN module AS m ON a.md_id = m.md_id WHERE ra.role_id = '角色ID'如果还要关联用户表 就在 WHERE前面加 LEFT JOIN sub AS s ON ra.role_id = s.role_id<br><font color="#e78608">------解决方案--------------------</font><br>功能放在相应的模块下面,表示模块id 是已知且固定的<br>又角色id已知<br>则连接应使用内连接或逗号连接,因为是要取得存在的功能<br><br>
SQL code
select* from role_auy, auy where role_auy.auy_id=auy.auy_id  and role_auy.role_id=角色id  and auy.md_id=模块id<div class="clear">
                 
              
              
        
            </div>
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn