欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mybatis中SQL语句运用总结

程序员文章站 2023-11-09 08:43:58
union 连接查询 连接两个表后会过滤掉重复的值 and trans_type = #{transType,jdbcType=TINYINT}

union 连接查询  连接两个表后会过滤掉重复的值

<resultmap id="baseresultmap" type="com.sprucetec.pay.etl.model.billdetail">
        <id column="id" jdbctype="integer" property="id"/>
        <result column="pay_order_no" jdbctype="varchar" property="payorderno"/>
        <result column="pay_channel_id" jdbctype="tinyint" property="paychannelid"/>
        <result column="pay_amount" jdbctype="integer" property="payamount"/>
        <result column="trans_date" jdbctype="integer" property="transdate"/>
        <result column="trans_type" jdbctype="varchar" property="transtype"/>
        <result column="error_type" jdbctype="varchar" property="errortype"/>
        <result column="is_check" jdbctype="tinyint" property="ischeck"/>
    </resultmap>

  <sql id="condition">
  <if test="transtype != null">
    and trans_type = #{transtype,jdbctype=tinyint}
  </if>
  <if test="paychannelid != null">
    and pay_channel_id = #{paychannelid,jdbctype=tinyint}
  </if>

 </sql>

<select id="querylist" parametertype="com.pay.billcheckquery" resultmap="baseresultmap">
       select a.pay_order_no,a.trans_date,a.pay_amount,a.pay_channel_id,a.trans_type,a.error_type
       from (select pay_order_no,trans_date,pay_amount,pay_channel_id,trans_type,"无结果" as error_type
       from t_pay_core_order
       where 1 = 1 <include refid="condition"/>
       union 
       select pay_order_no,trans_date,pay_amount,pay_channel_id,trans_type,"缺失" as error_type
       from t_pay_bill_file_detail 
       where 1 = 1 <include refid="condition"/>
       ) as a
       order by a.trans_date desc
       limit #{pagesize} offset #{startrecord}
    </select>

union all 才可以将所有的值都查询出来,自己将所有的值查询完总是少,才发现是这个问题

<select id="querycountandsum" parametertype="com.billcheckquery" resultmap="baseresultmap">
       select sum(a.pay_amount) as trans_amount,count(1) as trans_num from 
       (select pay_amount from t_pay_core_order
       where 
        pay_channel_id = #{paychannelid,jdbctype=smallint}and trans_date &gt;= #{starttime,jdbctype=integer}
        and trans_date &lt;= #{endtime,jdbctype=integer}union all
       select pay_amount from t_pay_bill_file_detail 
       where 
        pay_channel_id = #{paychannelid,jdbctype=smallint}and trans_date &gt;= #{starttime,jdbctype=integer}
        and trans_date &lt;= #{endtime,jdbctype=integer}
       ) as a
    </select>

传入对象中有list需要使用时,需要进行遍历,我在in语句中使用

<update id="updatebynum" parametertype="com.query.billcheckquery">
         update t_pay_core_order t1,t_pay_bill_file_detail t2
           set t1.is_check = 1,t2.is_check = 1
        where 
        t1.pay_order_no = t2.pay_order_no
        and t2.pay_order_no in
        <foreach item="payorderno" index="index" collection="orderlist" open="(" separator="," close=")">
                #{payorderno,jdbctype=varchar}
        </foreach>
        and t1.trans_date &gt;= #{starttime,jdbctype=integer} 
        and t1.trans_date &lt;= #{endtime,jdbctype=integer}</update>

或者直接在list中储存对象也可以遍历取出值

<insert id="batchinsert" parametertype="java.util.list" >
        insert into t_pay_bill_file_detail (file_id,pay_order_no,third_trade_no,trans_type,
        pay_channel_id,pay_amount,trans_date)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.payorderno},
            #{item.transtype},
            #{item.transdate}
            )
        </foreach>
    </insert>