微人事中的联合查询

前言

　　在我的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.*表示自然联结，指表中的所有

　　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>