多表连接查询

1、查询用户拥有的权限

子查询和连接查询2种，比较推荐连接查询，子查询比较影响性能，子查询看起来比较容易理解，而连接查询需要比较熟悉各个表之间的关系

SELECT DISTINCT p.* FROM t_permission p
    LEFT JOIN t_role_permission trp ON trp.`permissionid`=p.`id`
    LEFT JOIN t_role tr ON tr.`id`=trp.`roleid`
    WHERE tr.id IN(
        SELECT t_r.id FROM t_role t_r
        LEFT JOIN t_user_role t_u_r
            ON t_u_r.`roleid`=t_r.`id`
        WHERE t_u_r.`userid`=31
    );

子查询不太推荐，比较影响性能
推荐连接查询
SELECT DISTINCT p.* FROM t_permission p
    LEFT JOIN t_role_permission trp ON trp.`permissionid`=p.`id`
    LEFT JOIN t_role tr ON tr.`id`=trp.`roleid`
    LEFT JOIN t_user_role tur ON tur.`roleid`=tr.`id`
    LEFT JOIN t_user tu ON tu.`id`=tur.`userid`
    WHERE tu.`id`=31;

本文由博客一文多发平台 OpenWrite 发布！