充分利用inner join 进行多表联查的优化

题目：找到所有拥有这个权标识（pemission）的用户

sys_menu 里面有权限标识 permission ，通过permission找到对应的menu_id
再通过menu_id在对应的sys_menus_rolse表里面找到对应的role_id
通过role_id 在sys_users_roles中找到对应的user_id
最后，用role_id在user表格中找到对应的user

表结构：

连接：招聘系统DEV
    数据库：ntby_zp
    	表：sys_user
               sys_menu
               sys_menus_roles
               sys_users_roles

1. 原代码：(通过角色id查询用户信息)

SELECT sys_user.* FROM sys_user inner join (SELECT user_id FROM sys_users_roles where role_id =:roleId) as u on sys_user.user_id = u.user_id

2. 辅助分析代码
   这里可以在sys_menu, sys_roles_menu两个表中查到permission对应的roleId

SELECT sys_roles_menus.role_id 
FROM `sys_roles_menus` 
inner join

(SELECT menu_id 
FROM `sys_menu` 
where permission = "customerManagerLogo") as r 
on sys_roles_menus.menu_id = r.menu_id 

3. navicat中运行成功的代码

SELECT sys_user.* 
FROM sys_user 
inner join 

(SELECT user_id 
FROM sys_users_roles 
where role_id 

in 
(SELECT sys_roles_menus.role_id 
FROM `sys_roles_menus` 
inner join 

(SELECT menu_id 
FROM `sys_menu` 
where permission = "customerManagerLogo") as r 
on sys_roles_menus.menu_id = r.menu_id)) as u 

on sys_user.user_id = u.user_id

分析： 这种方法是先把条件查出来，然后再通过条件一层一层的往外扩大范围，去查询，表结构不清晰，比较不好梳理。

-- 复制到代码中
SELECT sys_user.* FROM sys_user inner join (SELECT user_id FROM sys_users_roles where role_id in (SELECT sys_roles_menus.role_id FROM `sys_roles_menus` inner join (SELECT menu_id FROM `sys_menu` where permission = :permission) as r on sys_roles_menus.menu_id = r.menu_id)) as u on sys_user.user_id = u.user_id

4. 充分的利用inner join进行多表联查

select *,group_concat(distinct a.nick_name) 
from sys_user a
inner join sys_users_roles b
on a.user_id = b.user_id
inner join sys_roles_menus c
on b.role_id = c.role_id
inner join sys_menu d
on c.menu_id = d.menu_id
where permission = 'customerManager'
group by a.nick_name;

最后一列会多一列，但是并不影响数据的使用

这种方式是充分的利用了inner join 的特性，利用inner join 会对多个表进行连接，产生笛卡尔积的特性，
按照范围从大到小的方式，不断的缩小范围，从一个大的范围里面不断的加条件，从而找到自己想要的数据。

注意：

1. 有几个表是取决于你查询的信息，以及连接条件中，是否有用到了这个表，用到则加，没用到则不要加。
2. 在连接的时候，每连接一个表给一个表起一个别名，这样方便后面加条件。
3. a b c d 4张表连接好以后，想取哪个表的字段就加上哪个表的字段
   ，想用哪个表筛选 就用哪个表筛选。
4. 由于使用inner join，是利用了产生笛卡尔积的原理，所以可能有重复的数据的情况。在本题目中nick_name字段就出现了重复的情况。
   方法一：
   利用了这种结构

select *,group_concat(distinct name) from table group by name;

其中：
group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。比较抽象，难以理解。
通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

所以要对该字段进行去重，同时还要把user里面的其他所有的字段还是出来，由于distinct只能对单个的字段进行去重，且只有写在开头才有作用。就有了这样的写法：

select distinct a.nick_name,a.* 
from sys_user a
inner join sys_users_roles b
on a.user_id = b.user_id
inner join sys_roles_menus c
on b.role_id = c.role_id
inner join sys_menu d
on c.menu_id = d.menu_id
where permission = 'customerManager'

结果：这种方式，多出来的一列在第一列