mybatis---复杂sql

1 查询实体类，其中一个字段的值在另外一张表中

实体类字段定义
@TableField(exist=false)
private String createUserName;

<select id="getMaterialApplyByBillNum" resultMap="materialApply">
        select apply.*,
            (select emp_name from employee emp where emp.emp_code = apply.create_user_code) as createUserName
        from material_apply apply
        where bill_num = #{billNum}
    </select>

2 模糊查询

    select *
    from material_apply 
    where create_user_code = #{createUserCode}
          and logic_status = 1
          and bill_num like concat('%',#{billNum},'%')；

3 union 组合多表查询的结果

select(
   select *
        frommaterial_apply where create_user_code = #{createUserCode} and logic_status = 1
   union
   select *
        from  clothes_apply where apply_user_code = #{createUserCode} and logic_status = 1
 ) t order by  create_time desc