前言
在我的mapper接口中常见的增删改查这篇博客中，我已经详细地介绍了项目中常见的增删改查接口和实现；在本篇文章中，我会将微人事中的联表查询写在下面
1.联表查询概述
我在微人事EmployeeMapper、MenuMapper中遇到了很多与联表查询相关的mysql知识点；主要的联表查询可分为子查询（等值查询）、内部查询（内部查询）、外部查询（左外部查询、右外部查询）以及高级查询（自联结、自然联结）
2.使用联表查询
2.1 子查询
子查询就是嵌套在其他查询中的查询
-- 改代码的数据库表来源与MySQL必知必会，大家可以下载这本书
-- 设计一个订单表orders、订单物品表orderitms、客户信息表customers
-- 实例：显示出订单物品为TNT2的所有客户列表
-- 1）检索出包含物品TNT2的所有订单号
-- 2）检索出前一步骤的所有订单号的客户ID
-- 3）检索前一步骤返回的所有客户的所有信息
-- 使用普通的select语句实现
select order_num from orders where prod_id = 'TNT2';
select cust_id from orderitems where order_num in (20005, 20007);
select cust_name, cust_contact, from customers where cust_id in (10001, 10004);
-- 使用子查询实现
select cust_name, cust_contact
from customers
where cust_id in (select cust_id
from orderitems
where order_num in (
select order_num
from orders
where prod_id = 'TNT2')
);
-- 把要查询的内容放在select后面，查询的步骤有里到外
子查询实例
2.2 内部查询
内部查询等同于上面子查询中的等值查询
-- 该商品供应表vendors与商品表products均来源于MySQL必知必会
-- 1.作为计算字段使用子查询
-- 显示customers表中的，每个客户的订单数目
select cust_name, cust_state, (select COUNT(*)
from orders
where order.cust_id = orderitems.cust_id) as orders
from customers
order by cust_name;
-- 将子查询的结果作为字段返回，其中order by表示排序，默认是升序order by asc，还有降序order by desc
-- 2.内部联结（如果没有联结条件，那么会形成笛卡尔积是的检索的结果与实际不符）
-- 创建供应商表与产品表联结
-- 2.1 使用等值联结实现
select vend_name, prod_name, prod_id
from vendors as v, products as p
where v.vend_id = p.vend_id
order by vend_name, prod_name;
-- 2.2 使用内部联结实现
select vend_name, prod_name, prod_id
from vendors as v inner join products as p
on v.vend_id = p.vend_id
order by vend_name, prod_name;
等值查询与内部查询
2.3 外部查询
left join on是以左表为主表，检索出左表的所有数据以及左表与右表交集的部分，同理right join on 是同样的原理
自然联结排除字段多次出现，使其出现一次
<select id="getEmployeeByPageWithSalary" resultMap="EmployeeWithSalary">
select e.*, d.`name` as dname, s.`id` as sid, s.`accumulation_fund_base` as
saccumulationFundBase, s.`accumulation_fund_per` as saccumulationFundPer, s.`all_salary` as
sallSalary, s.`basic_salary` as sbasicSalary, s.`bonus` as sbonus, s.`create_date` as screateDate,
s.`lunch_salary` as slunchSalary, s.`medical_base` as smedicalBase, s.`medical_per` as smedicalPer,
s.`name` as sname,s.`pension_base` as spensionBase, s.`pension_per` as spensionPer,
s.`traffic_salary` as strafficSalary FROM vhr_project.employee e LEFT JOIN vhr_project.emp_salary es ON e.`id`=es.`eid`
LEFT JOIN vhr_project.salary s ON es.`sid`=s.`id` LEFT JOIN vhr_project.department d ON e.`department_id`=d.`id`
order by e.id
<if test="page !=null and size !=null">
limit #{page},#{size}
</if>
</select>
-- 其中的e.*表示自然联结，指表中的所有
微人事中的left join on 与自然联结
2.4 高级查询
<resultMap id="BaseResultMap" type="com.ku.vhr.model.Menu" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="url" property="url" jdbcType="VARCHAR" />
<result column="path" property="path" jdbcType="VARCHAR" />
<result column="component" property="component" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="icon_cls" property="iconCls" jdbcType="VARCHAR" />
<result column="keep_alive" property="keepAlive" jdbcType="BIT" />
<result column="require_auth" property="requireAuth" jdbcType="BIT" />
<result column="parent_id" property="parentId" jdbcType="INTEGER" />
<result column="enabled" property="enabled" jdbcType="BIT" />
</resultMap>
<resultMap id="MenuWithRole" type="com.ku.vhr.model.Menu" extends="BaseResultMap">
<collection property="roles" ofType="com.ku.vhr.model.Role">
<id column="rid" property="id"/>
<result column="rname" property="name"/>
<result column="rnameZh" property="nameZh"/>
</collection>
</resultMap>
<!--它的子类-->
<resultMap id="Menus2" type="com.ku.vhr.model.Menu" extends="BaseResultMap">
<collection property="children" ofType="com.ku.vhr.model.Menu">
<id column="id2" property="id" jdbcType="INTEGER" />
<result column="url2" property="url" jdbcType="VARCHAR" />
<result column="path2" property="path" jdbcType="VARCHAR" />
<result column="component2" property="component" jdbcType="VARCHAR" />
<result column="name2" property="name" jdbcType="VARCHAR" />
<result column="iconCls2" property="iconCls" jdbcType="VARCHAR" />
<result column="keep_alive2" property="keepAlive" jdbcType="BIT" />
<result column="require_auth2" property="requireAuth" jdbcType="BIT" />
<result column="parentId2" property="parentId" jdbcType="INTEGER" />
<result column="enabled2" property="enabled" jdbcType="BIT" />
</collection>
</resultMap>
<resultMap id="MenuWithChildren" type="com.ku.vhr.model.Menu" extends="BaseResultMap">
<id column="id1" property="id"/>
<result column="name1" property="name"/>
<collection property="children" ofType="com.ku.vhr.model.Menu">
<id column="id2" property="id"/>
<result column="name2" property="name"/>
<collection property="children" ofType="com.ku.vhr.model.Menu">
<id column="id3" property="id"/>
<result column="name3" property="name"/>
</collection>
</collection>
</resultMap>
-- 1.微人事中的自联结出现在Menupper中的getAllMenus()方法中
<select id="getAllMenus" resultMap="MenuWithChildren">
select m1.`id` as id1, m1.`name` as name1, m2.`id` as id2, m2.`name` as name2,
m3.`id` as id3, m3.`name` as name3 from vhr_project.menu m1, vhr_project.menu m2, vhr_project.menu m3
where m1.`id`=m2.`parent_id` and m2.`id`=m3.`parent_id` and m3.`enabled`=true
order by m1.`id`,m2.`id`,m3.`id`
</select>
-- 此处的自联结，是以父子关系存在的，因为菜单之间存在这种父子关系
-- 2.微人事中的自然联结出现在Menupper中的getAllWithRole()方法与getMenusByHrId()中
<select id="getAllMenusWithRole" resultMap="MenuWithRole">
select m.*, r.`id` as rid, r.`name` as rname, r.`nameZh` as rnameZh
from vhr_project.menu m, vhr_project.menu_role mr, vhr_project.role r
where m.`id`=mr.`mid`
and mr.`rid`=r.`id`
order by m.`id`
</select>
<select id="getMenusByHrId" resultMap="Menus2">
select distinct m1.*, m2.`id` as id2, m2.`component` as component2, m2.`enabled` as enabled2,
m2.`icon_cls` as iconCls2, m2.`keep_alive` as keepAlive2, m2.`name` as name2,
m2.`parent_id` as parentId2, m2.`require_auth` as requireAuth2, m2.`path` as path2
from vhr_project.menu m1, vhr_project.menu m2, vhr_project.hr_role hrr,vhr_project.menu_role mr
where m1.`id`=m2.`parent_id`
and hrr.`hrid`=#{hrid}
and hrr.`rid`=mr.`rid`
and mr.`mid`=m2.`id`
and m2.`enabled`=true
order by m1.`id`,m2.`id`
</select>
微人事中的自联结