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 >= #{starttime,jdbctype=integer} and trans_date <= #{endtime,jdbctype=integer}union all select pay_amount from t_pay_bill_file_detail where pay_channel_id = #{paychannelid,jdbctype=smallint}and trans_date >= #{starttime,jdbctype=integer} and trans_date <= #{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 >= #{starttime,jdbctype=integer} and t1.trans_date <= #{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>
推荐阅读
-
Mysql数据库之常用sql语句进阶与总结
-
C#中实现一次执行多条带GO的sql语句实例
-
mybatis中SQL语句运用总结
-
JSP中操作数据库的常用SQL标签用法总结
-
sql server中datetime字段去除时间的语句
-
mssql函数DATENAME使用示例讲解(取得当前年月日/一年中第几天SQL语句)
-
Javascript中for循环语句的几种写法总结对比
-
Mybatis中的动态SQL语句解析
-
如何在SQL SERVER 2005存储过程中,使用循环语句
-
Mybaits 源码解析 (六)----- 全网最详细:Select 语句的执行过程分析(上篇)(Mapper方法是如何调用到XML中的SQL的?)