使用case when 查询不同角色不同的内容

 

使用case  when 查询不同角色不同的内容

主要针对其他表，不同字段和角色比对，不是同一个字段和角色比对的简单情况

 

case when  可以放在任何地方

 

当然也可以用mybatise本身的标签：

<choose>

 

复杂的sql都可借助网上资料查询结构，思路

 

<select id="selectByCondition" parameterType="map" resultMap="BaseResultMap">

    <include refid="PAGINATION.mysql_paginationStart" />

    SELECT

    *,

(SELECT TEAM_NAME FROM financial_sales_team t WHERE t.ID = o.TEM_ID) AS TEAM_NAME,

(SELECT EMP_NAME FROM financial_sales_employment t WHERE t.ID = o.EMP_ID) AS EMP_NAME

    FROM

    financial_sales_order o

    <include refid="where_condition" />

    <include refid="PAGINATION.mysql_paginationEnd" />

  </select>

 

 

<sql id="where_condition">

    <where>

      <if test="userId != null and userId != ''" >

      <!-- 不同的角色查看属于自己团队或分配给自己的数据,经理只能查看自己导入的数据，管理员可以查看全部的数据 -->

        AND (CASE 

WHEN (SELECT ROLE_CODE FROM financial_sales_role WHERE ID = (SELECT ROLE_ID FROM financial_sales_user_role WHERE USER_ID = #{userId})) = 0 

THEN o.EMP_ID = (SELECT EMPID FROM financial_sales_user WHERE ID = #{userId})

WHEN (SELECT ROLE_CODE FROM financial_sales_role WHERE ID = (SELECT ROLE_ID FROM financial_sales_user_role WHERE USER_ID = #{userId})) = 1 

THEN o.TEM_ID = (SELECT TEMID FROM financial_sales_employment WHERE USER_ID = #{userId})

WHEN (SELECT ROLE_CODE FROM financial_sales_role WHERE ID = (SELECT ROLE_ID FROM financial_sales_user_role WHERE USER_ID = #{userId})) = 2 

THEN o.IMPORTER_ID = (SELECT EMPID FROM financial_sales_user WHERE ID = #{userId})

ELSE TRUE 

END)

      </if>

      <if test="batchName != null and batchName != ''" >

        AND o.BATCH_NAME like CONCAT('%',#{batchName},'%' )

      </if>

      <if test="customerId != null and customerId != ''" >

        AND o.CUSTOMER_ID=#{customerId}

      </if>

      <choose>

<when test="empId != null and empId != ''" >

        AND o.EMP_ID = #{empId}

</when>

<otherwise>

      <if test="temId != null and temId != ''" >

        AND o.TEM_ID = #{temId}

      </if>

</otherwise>

  </choose>

      <if test="orderno != null and orderno != ''" >

        AND o.ORDERNO like CONCAT('%',#{orderno},'%' )

      </if>

      <if test="customerName != null and customerName != ''" >

        AND o.CUSTOMER_NAME like CONCAT('%',#{customerName},'%' )

      </if>

      <if test="prassignTime != null  and prassignTime != ''" >

        and o.ASSIGN_TIME  &gt;= #{prassignTime,jdbcType=VARCHAR}

      </if>

      <if test="lsassignTime != null and lsassignTime != ''" >

        and o.ASSIGN_TIME &lt;= #{lsassignTime,jdbcType=VARCHAR}

      </if>

      <if test="prfirstdailTime != null  and prfirstdailTime != ''" >

        and o.FIRSTDAIL_TIME  &gt;= #{prfirstdailTime,jdbcType=VARCHAR}

      </if>

      <if test="lsfirstdailTime != nul and lsfirstdailTime != ''" >

        and o.FIRSTDAIL_TIME  &lt;= #{lsfirstdailTime,jdbcType=VARCHAR}

      </if>

      <if test="status != null and status != ''" >

        AND o.STATUS = #{status}

      </if>

      <if test="prlastdailTime != null and prlastdailTime != ''" >

        and o.LASTDAIL_TIME  &gt;= #{prlastdailTime,jdbcType=VARCHAR}

      </if>

 

      <if test="lslastdailTime != null and lslastdailTime != ''" >

        and o.LASTDAIL_TIME &gt;= #{lslastdailTime,jdbcType=VARCHAR}

      </if>

      <if test="dialResult != null and dialResult != ''" >

        AND o.DIAL_RESULT = #{dialResult}

      </if>

      <if test="(prdailCount != null and prdailCount != '') and (lsdailCount != null and lsdailCount != '')" >

        AND o.DAIL_COUNT BETWEEN #{prdailCount} AND #{lsdailCount}

      </if>

      <if test="assignStatus != null and assignStatus != ''" >

        AND o.ASSIGNSTATUS = #{assignStatus}

      </if>

      <if test="importerId != null" >

        AND o.IMPORTER_ID = #{importerId}

      </if>

      <if test="orderType != null and orderType != ''" >

        AND o.ORDER_TYPE = #{orderType}

      </if>

    </where>

  </sql>

 

 

 

 

 

 

 

 

 

用于分页的sql:

 

PAGINATION:

 

 

 

 

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="PAGINATION">

  <sql id="mysql_paginationStart">

      <!-- <if test="offset != null and pageSize != null and isPaging==true">

       select * from ( 

      </if>

      -->

  </sql>

  <sql id="mysql_paginationEnd">

      <if test="offset != null and pageSize != null and isPaging==true">

               <!-- )tmp limit #{offset},#{pageSize} -->

           limit #{offset},#{pageSize}

      </if>

  </sql>

</mapper>