本文介绍了在MyBatis中如何使用动态SQL进行查询，包括MySQL的find_in_set函数判断和MyBatis的通用查询映射、插入、删除、更新等操作示例，适用于Oracle和MySQL数据库。
====================相关函数==================
mysql:find_in_set(#{areaCode},t4.parent_codes) 判断parent_codes列中是否含有areaCode这个值(级查询)
====================template==================
<?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="com.nari.smartpdroom.alarm.center.mapper.MalfunctionMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.nari.smartpdroom.alarm.center.beans.Malfunction">
<id column="ID" property="id"/>
<result column="MALFUNCTION_NO" property="malfunctionNo"/>
<result column="CUST_ID" property="custId"/>
<result column="MEASURE_ID" property="measureId"/>
</resultMap>
<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
ID, MALFUNCTION_NO, CUST_ID, MEASURE_ID, BUSI_CODE, MALFUNCTION_TYPE, MALFUNCTION_LEVEL, MALFUNCTION_CONTENT, MALFUNCTION_TIME, OPTION, OPERATOR, STATUS_CODE, PRONVINCE_CODE, CITY_CODE, COUNTY_CODE, ADDRESS, LONGITUDE, LATITUDE, COMMENT, DATA_SOURCE
</sql>
<sql id="malfunction_common_sql">
SELECT
W.ID,
W.MALFUNCTION_NO,
W.CUST_ID,
W.MEASURE_ID,
W.BUSI_CODE,
W.MALFUNCTION_TYPE,
W.MALFUNCTION_LEVEL,
W.MALFUNCTION_CONTENT,
DATE_FORMAT(W.MALFUNCTION_TIME,'%Y-%m-%d %H:%i:%s') MALFUNCTION_TIME,
W.`OPTION`,
W.OPERATOR,
DATE_FORMAT(W.OPERAT_TIME,'%Y-%m-%d %H:%i:%s') OPERAT_TIME,
W.STATUS_CODE,
W.PRONVINCE_CODE,
W.CITY_CODE,
W.COUNTY_CODE,
W.ADDRESS,
W.LONGITUDE,
W.LATITUDE,
W.`COMMENT`,
DATA_SOURCE,
M.`NAME` MEASURE_NAME,
C.`NAME` CUST_NAME,
M.CONTAINER_ID,
T.`NAME` CONTAINER_NAME,
D.`NAME` ASSIST_NAME,
CONCAT(A.`NO`,A.`NAME`) DEVICE_NAME
FROM
T_ALARM_MALFUNCTION W
LEFT JOIN T_COLL_ARCHIVE_MEASURE M ON W.MEASURE_ID = M.ID
LEFT JOIN T_CUST_CUSTOMER C ON W.CUST_ID = C.ID
LEFT JOIN T_ASSET_CONTAINER T ON M.CONTAINER_ID = T.ID
LEFT JOIN T_ASSET_DEVICE D ON D.ID = M.DEVICE_ID
LEFT JOIN T_ASSET_ASSIST_CABINET A ON A.ID=M.CHILD_CONTAINER_ID
</sql>
<insert id="insertPointData" parameterType="java.util.Map">
INSERT INTO t_coll_apply_mrd_ext (
ID,
DATA_ITEM_CODE,
MEARURE_ID,
STATUS_CODE,
REAL_VAVLUE,
UPDATE_TIME
)
VALUES
(
#{id},
#{code},
#{measureId},
#{status},
#{value},
now()
)
</insert>
<delete id="deleteByMesureIdAndDataItem" parameterType="java.util.Map">
DELETE FROM t_alarm_quota
<where>
<foreach collection="list" index="index" item="item" open=""
separator="OR" close="">
(MEASURE_ID=#{item.measureId} AND DATA_ITEM_CODE=#{item.dataItem})
</foreach>
</where>
</delete>
<update id="updateByMeasureIdAndDataItem" parameterType="com.nari.smartpdroom.alarm.center.beans.Quota">
UPDATE t_alarm_quota w
SET
w.MEASURE_ID=#{measureId},
w.DATA_ITEM_CODE=#{dataItemCode},
w.MEMO=#{memo}
<where>
w.MEASURE_ID=#{measureId}
AND w.DATA_ITEM_CODE=#{dataItemCode}
</where>
</update>
<update id="updatePointData" parameterType="java.util.Map">
UPDATE t_coll_apply_mrd_ext t
SET t.DATA_ITEM_CODE=#{code},t.MEARURE_ID=#{measureId},t.REAL_VAVLUE =#{value}, t.STATUS_CODE =#{status}, t.UPDATE_TIME = NOW()
WHERE
t.ID =#{id}
</update>
<select id="getCertainDictOptions" resultType="java.util.Map">
SELECT
LABEL, VALUE
FROM SYS_DICT_SINGLE
WHERE TYPE_ID = #{typeId}
AND VALUE IN
<foreach collection="optionValues" index="index" item="i" open="("
separator="," close=")">
#{i}
</foreach>
ORDER BY SORT
</select>
<select id="getCityDict" resultType="java.util.Map">
SELECT
id AS `VALUE`,
`name` AS LABEL
FROM
city
<where>
<if test="parentid != null and parentid.trim()!=''"> .trim()去空格
AND parentid =#{parentid}
</if>
<if test="leveltype != null and leveltype.trim()!=''">
AND leveltype =#{leveltype}
</if>
<if test="citycode != null and citycode.trim()!=''">
AND id =#{citycode}
</if>
<if test="preCode != null and preCode.trim()!=''">
AND id like concat(#{preCode},'%')
</if>
</where>
ORDER BY id
</select>
<select id="relationDate" resultType="java.util.Map">
SELECT
t6.ID,
IFNULL(t6.PID, t6.CONTAINER_ID) pid,
t6. NAME,
t7.DATA_ITEM_CODE
FROM
t_coll_archive_measure t6
INNER JOIN (
SELECT t1.ID,t1.CUST_ID pId,t1. NAME
FROM t_asset_container t1
INNER JOIN (
SELECT t2.id CUST_ID FROM t_cust_customer t2 WHERE t2.id = #{custId}
UNION ALL SELECT t3.CUST_ID id FROM t_cust_customer_rela t3,t_cust_customer t2 WHERE t3.CUST_ID = t2.id AND t3.DOMAIN_CUST_ID = #{custId}
) b
) c ON t6.pid = c.ID
LEFT JOIN t_coll_pro_pointno t7 ON t6.ID = t7.MEASURE_ID
</select>
<select id="selectMalfunctionListPage" resultMap="BaseResultMap">
<include refid="malfunction_common_sql"/>
<where>
<if test="malfunction.beginTime!=null">
AND DATE(W.MALFUNCTION_TIME) >=#{malfunction.beginTime}
</if>
<if test="malfunction.endTime!=null">
AND DATE(W.MALFUNCTION_TIME) <=#{malfunction.endTime}
</if>
<if test="malfunction.pronvinceCode!=null and malfunction.pronvinceCode!=''">
AND W.PRONVINCE_CODE =#{malfunction.pronvinceCode}
</if>
<if test="malfunction.cityCode!=null and malfunction.cityCode!=''">
AND W.CITY_CODE = #{malfunction.cityCode}
</if>
<if test="malfunction.custName!=null and malfunction.custName!=''">
AND C.`NAME` like CONCAT('%', #{malfunction.custName}, '%')
</if>
<if test="malfunction.deviceName!=null and malfunction.deviceName!=''">
AND CONCAT(A.`NO`,A.`NAME`) like CONCAT('%', #{malfunction.deviceName}, '%')
</if>
</where>
GROUP by W.ID DESC
limit #{offset},#{size}
</select>
<select id="selectByParams" resultType="int">
SELECT COUNT(0) COUNT FROM T_ALARM_MALFUNCTION W
<where>
<if test="1==1">
AND W.MALFUNCTION_TIME>DATE_SUB(now(), interval 1 hour)
</if>
<if test="malfunction.measureId!=null and malfunction.measureId!=''">
AND W.MEASURE_ID = #{malfunction.measureId}
</if>
</where>
ORDER BY W.MALFUNCTION_TIME DESC
</select>
<select id="qeuryByMalfunctionId" resultMap="BaseResultMap">
<include refid="malfunction_common_sql"/>
WHERE W.ID=#{id}
</select>
<update id="deal" parameterType="com.nari.smartpdroom.alarm.center.beans.Warn">
UPDATE t_alarm_malfunction t
<set>
<if test="statusCode != null">
t.STATUS_CODE =#{statusCode},
</if>
<if test="option != null">
t.`OPTION`= #{option},
</if>
t.OPERAT_TIME= NOW(),
</set>
WHERE
t.ID =#{id}
</update>
<select id="selectMalfunctionCount" resultType="int">
SELECT COUNT(0) count
FROM
T_ALARM_MALFUNCTION W
LEFT JOIN T_COLL_ARCHIVE_MEASURE M ON W.MEASURE_ID = M.ID
LEFT JOIN T_CUST_CUSTOMER C ON W.CUST_ID = C.ID
LEFT JOIN T_ASSET_CONTAINER T ON M.CONTAINER_ID = T.ID
LEFT JOIN T_ASSET_DEVICE D ON D.ID = M.DEVICE_ID
LEFT JOIN T_ASSET_ASSIST_CABINET A ON A.ID=M.CHILD_CONTAINER_ID
<where>
<if test="malfunction.beginTime!=null">
AND DATE(W.MALFUNCTION_TIME) >=#{malfunction.beginTime}
</if>
<if test="malfunction.endTime!=null">
AND DATE(W.MALFUNCTION_TIME) <=#{malfunction.endTime}
</if>
<if test="malfunction.pronvinceCode!=null and malfunction.pronvinceCode!=''">
AND W.PRONVINCE_CODE =#{malfunction.pronvinceCode}
</if>
<if test="malfunction.custName!=null and malfunction.custName!=''">
AND C.`NAME` like CONCAT('%', #{malfunction.custName}, '%')
</if>
<if test="malfunction.deviceName!=null and malfunction.deviceName!=''">
AND CONCAT(A.`NO`,A.`NAME`) like CONCAT('%', #{malfunction.deviceName}, '%')
</if>
</where>
</select>
</mapper>
===============================================
SELECT
RIGHT (?, 2) AS "moryue",
j.ROWNO AS "ROWNO",
k.num AS "num",
( CASE WHEN k.num IS NULL THEN (SELECT dict.label FROM sys_dict dict WHERE dict.VALUE = 'no_zgtz')
ELSE CONCAT((SELECT dict.label FROM sys_dict dict WHERE dict.VALUE = 'head_zgtz')," ",k.num," ",(SELECT dict.label FROM sys_dict dict WHERE dict.VALUE = 'foot_zgtz'))
END
) AS 'recti',
b.NAME AS "deptName",
a.NAME AS "projectName",
CONCAT( a.date_begin," ",(SELECT dict.label FROM sys_dict dict WHERE dict.VALUE = 'to_public')," ",a.date_end) AS "interval",
CONCAT(c.NAME, d.label) AS "roadName",
f.label AS "roadType",
g.label AS "buildType",
n.times AS "times",
n.mark AS "mark",
n.task_content AS "taskContent"
FROM
sgjhgl_plan_prediction a
LEFT JOIN sys_office b ON b.id = a.dept_id
LEFT JOIN sys_road c ON c.id = a.road
LEFT JOIN sys_dict d ON d.VALUE = a.road_direc
LEFT JOIN (SELECT e.* FROM sys_dict e WHERE e.type = 'fdxs') f ON f.VALUE = a.road_closure_type
LEFT JOIN sys_dict g ON g.VALUE = a.build_type
LEFT JOIN (SELECT @ROWNO: = @ROWNO + 1 AS ROWNO,i.id AS "id" FROM
(
SELECT
h.prediction_id AS "id"
FROM
sgjhgl_plan_prediction h
WHERE
h.STATUS != '0'
AND h.STATUS != '1'
AND h.del_flag = '0'
AND h.create_date LIKE CONCAT('%', LEFT(?, 4), '%')
AND h.date_begin LIKE CONCAT('%', RIGHT(?, 2), '%')
ORDER BY
h.c
=========================================================================
=========================================================================
===============================ORACLE====================================
=========================================================================
select wlCode 物流商code,wlName 物流商名,supCode 供应商code,supName 供应商名,beg 起始地,der 目的地,wm_concat(distinct logType) 运输类型,
settleMonth,sum(fh_M) 月度发货量M,wm_concat(distinct pri_M) 运输单价M,
sum(fh_T) 月度发货量T,wm_concat(distinct pri_T)运输单价T,
wm_concat(distinct tiHuoFeiCiShu) 提货费次数合计,wm_concat(distinct songHuoFeiCiShu) 送货费次数合计,
wm_concat(distinct tiSongHuoFeiCiShu) 提送货费次数合计,wm_concat(distinct Vol) 装卸体积合计,
wm_concat(distinct wegh) 装卸重量合计
from
(select distinct
TMS_LONGDISTACE_TRANSPORTORDR.LTO_LOGCODE wlCode,TMS_LONGDISTACE_TRANSPORTORDR.LTO_LOGNAME wlName,
TMS_LOGN_SHIPMENTINDICTOR.OU_CODE supCode ,TMS_LOGN_SHIPMENTINDICTOR.OU_NAME supName,
TMS_CT_LC_PRICECLAUSE.LPC_BEGIN beg,TMS_CT_LC_PRICECLAUSE.LPC_END der,
TMS_LONGDISTACE_TRANSPORTORDR.LTO_TRANSPORTTYPE logType,
substr(TMS_LOGN_SHIPMENTINDICTOR.LTSI_DELIVERYDATE,1,7) settleMonth,
TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALVOLUME fh_M,
NVL2(TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALVOLUME,TMS_CT_LC_PRICECLAUSE.LPC_PRICE,null) pri_M,
TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALWEIGHT fh_T,
NVL2(TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALWEIGHT,TMS_CT_LC_PRICECLAUSE.LPC_PRICE,null) pri_T,
TMS_TRANSIT_VOLINFOREPORT.PICKUPCHARGENUM tiHuoFeiCiShu,TMS_TRANSIT_VOLINFOREPORT.DELIVERYNUM songHuoFeiCiShu,
TMS_TRANSIT_VOLINFOREPORT.PICKUPDLIVERYNUM tiSongHuoFeiCiShu,TMS_TRANSIT_VOLINFOREPORT.HANDLINGVOLUME Vol,
TMS_TRANSIT_VOLINFOREPORT.HANDLINGWEIGHT wegh
from TMS_LONGDISTACE_TRANSPORTORDR
left join TMS_CT_LC_PRICECLAUSE on TMS_CT_LC_PRICECLAUSE.LPC_ID = TMS_LONGDISTACE_TRANSPORTORDR.LTO_LPC_ID
left join TMS_LOGN_SHIPMENTINDICTOR on TMS_LONGDISTACE_TRANSPORTORDR.LTO_CODE=TMS_LOGN_SHIPMENTINDICTOR.LTO_CODE
left join TMS_TRANSIT_VOLINFOREPORT on TMS_LOGN_SHIPMENTINDICTOR.LTSI_ID=TMS_TRANSIT_VOLINFOREPORT.LTSI_ID) aa
where logType = 1
group by aa.settleMonth,aa.wlName,aa.wlCode,aa.supCode,aa.supName,aa.beg,aa.der
=========最终版=======
select wlCode 物流商code,wlName 物流商名,supCode 供应商code,supName 供应商名,beg 起始地,der 目的地,wm_concat(distinct logType) 运输类型,
settleMonth,
sum(fh_M) 月度发货量M,wm_concat(distinct tj) 运输单价M,
sum(fh_T) 月度发货量T,wm_concat(distinct zl)运输单价T,
wm_concat(distinct tiHuoFeiCiShu) 提货费次数合计,wm_concat(distinct songHuoFeiCiShu) 送货费次数合计,
wm_concat(distinct tiSongHuoFeiCiShu) 提送货费次数合计,wm_concat(distinct Vol) 装卸体积合计,
wm_concat(distinct wegh) 装卸重量合计
from
(select distinct
TMS_LONGDISTACE_TRANSPORTORDR.LTO_LOGCODE wlCode,TMS_LONGDISTACE_TRANSPORTORDR.LTO_LOGNAME wlName,
TMS_LOGN_SHIPMENTINDICTOR.OU_CODE supCode ,TMS_LOGN_SHIPMENTINDICTOR.OU_NAME supName,
aa.LPC_BEGIN beg,aa.LPC_END der,
TMS_LONGDISTACE_TRANSPORTORDR.LTO_TRANSPORTTYPE logType,
substr(TMS_LOGN_SHIPMENTINDICTOR.LTSI_DELIVERYDATE,1,7) settleMonth,
TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALVOLUME fh_M,
case when aa.LPC_SETTLEMENT=0 then aa.LPC_PRICE else bb.LPC_PRICE end as tj,
TMS_LOGN_SHIPMENTINDICTOR.LTSI_ACTUAL_TOTALWEIGHT fh_T,
case when aa.LPC_SETTLEMENT=1 then aa.LPC_PRICE else bb.LPC_PRICE end as zl,
TMS_TRANSIT_VOLINFOREPORT.PICKUPCHARGENUM tiHuoFeiCiShu,TMS_TRANSIT_VOLINFOREPORT.DELIVERYNUM songHuoFeiCiShu,
TMS_TRANSIT_VOLINFOREPORT.PICKUPDLIVERYNUM tiSongHuoFeiCiShu,TMS_TRANSIT_VOLINFOREPORT.HANDLINGVOLUME Vol,
TMS_TRANSIT_VOLINFOREPORT.HANDLINGWEIGHT wegh
from TMS_LONGDISTACE_TRANSPORTORDR
left join TMS_CT_LC_PRICECLAUSE aa on aa.LPC_ID = TMS_LONGDISTACE_TRANSPORTORDR.LTO_LPC_ID
left join TMS_CT_LC_PRICECLAUSE bb on bb.LPC_ID = TMS_LONGDISTACE_TRANSPORTORDR.LTO_LPC_ID2
left join TMS_LOGN_SHIPMENTINDICTOR on TMS_LONGDISTACE_TRANSPORTORDR.LTO_CODE=TMS_LOGN_SHIPMENTINDICTOR.LTO_CODE
left join TMS_TRANSIT_VOLINFOREPORT on TMS_LOGN_SHIPMENTINDICTOR.LTSI_ID=TMS_TRANSIT_VOLINFOREPORT.LTSI_ID
)AAA
where logType = 1
group by AAA.settleMonth,AAA.wlName,AAA.wlCode,AAA.supCode,AAA.supName,AAA.beg,AAA.der
=========merge into ================
<insert id="batchSave" parameterType="java.util.List">
MERGE INTO TMS_SETTLEMENT t
USING (
select
#{monthDelivery_M,jdbcType=VARCHAR2} SE_MONTHDELIVERY_MTT,
#{monthDelivery_T,jdbcType=VARCHAR2} SE_MONTHDELIVERY_TTT,
#{TiHuoTimes,jdbcType=VARCHAR2} SE_TIHUOTIMESTT,
#{SongTimes,jdbcType=VARCHAR2} SE_SONGTIMESTT,
#{tiSongTimes,jdbcType=VARCHAR2} SE_TISONGTIMESTT,
#{volumeLoadingTotal,jdbcType=VARCHAR2} SE_VOLUMELOADINGTOTALTT,
#{weightLoadingTotal,jdbcType=VARCHAR2} SE_WEIGHTLOADINGTOTALTT,
#{logcode,jdbcType=VARCHAR2} SE_LOGCODETT,
#{supcode,jdbcType=VARCHAR2} SE_SUPCODETT,
#{origin,jdbcType=VARCHAR2} SE_ORIGINTT,
#{destinationss,jdbcType=VARCHAR2} SE_DESTINATIONTT,
#{settleMonth,jdbcType=VARCHAR2} SE_SETTLEMONTHTT,
#{uuid,jdbcType=VARCHAR2} SE_UUIDTT,
#{accountCode,jdbcType=VARCHAR2} SE_ACCOUNTCODETT,
#{logname,jdbcType=VARCHAR2} SE_LOGNAMETT,
#{supname,jdbcType=VARCHAR2} SE_SUPNAMETT,
#{logtype,jdbcType=VARCHAR2} SE_LOGTYPETT,
#{unitPrice_M,jdbcType=VARCHAR2} SE_UNITPRICE_MTT,
#{unitPrice_T,jdbcType=VARCHAR2} SE_UNITPRICE_TTT,
#{additionalCost,jdbcType=VARCHAR2} SE_ADDITIONALCOSTTT,
#{MonthShipAccount,jdbcType=VARCHAR2} SE_MONTHSHIPACCOUNTTT,
#{differentAdjust,jdbcType=VARCHAR2} SE_DIFFERENTADJUSTTT,
#{rate,jdbcType=VARCHAR2} SE_RATETT,
#{MonthAccount,jdbcType=VARCHAR2} SE_MONTHACCOUNTTT,
#{createUser,jdbcType=VARCHAR2} SE_CREATEUSERTT,
#{createTime,jdbcType=VARCHAR2} SE_CREATETIMETT,
#{status,jdbcType=VARCHAR2} SE_STATUSTT
from dual
) tt
ON (t.SE_LOGCODE = tt.SE_LOGCODETT AND
t.SE_SUPCODE = tt.SE_SUPCODETT AND
t.SE_ORIGIN= tt.SE_ORIGINTT AND
t.SE_DESTINATION=tt.SE_DESTINATIONTT AND
t.SE_SETTLEMONTH=tt.SE_SETTLEMONTHTT )
WHEN MATCHED THEN
UPDATE SET t.SE_MONTHDELIVERY_M =(tt.SE_MONTHDELIVERY_MTT+t.SE_MONTHDELIVERY_M),
t.SE_MONTHDELIVERY_T =(tt.SE_MONTHDELIVERY_TTT+t.SE_MONTHDELIVERY_T),
t.SE_TIHUOTIMES =(tt.SE_TIHUOTIMESTT+t.SE_TIHUOTIMES),
t.SE_SONGTIMES =(tt.SE_SONGTIMESTT+t.SE_SONGTIMES),
t.SE_WEIGHTLOADINGTOTAL =(tt.SE_TISONGTIMESTT+t.SE_TISONGTIMES),
t.SE_TISONGTIMES =(tt.SE_WEIGHTLOADINGTOTALTT+t.SE_WEIGHTLOADINGTOTAL),
t.SE_VOLUMELOADINGTOTAL =(tt.SE_VOLUMELOADINGTOTALTT+t.SE_VOLUMELOADINGTOTAL),
WHEN NOT MATCHED THEN
INSERT(SE_UUID,SE_ACCOUNTCODE,SE_LOGCODE,SE_LOGNAME,SE_SUPCODE,SE_SUPNAME,SE_ORIGIN,SE_DESTINATION,SE_LOGTYPE,
SE_SETTLEMONTH,SE_MONTHDELIVERY_M,SE_UNITPRICE_M,SE_MONTHDELIVERY_T,SE_UNITPRICE_T,SE_TIHUOTIMES,SE_SONGTIMES,
SE_TISONGTIMES,SE_VOLUMELOADINGTOTAL,SE_WEIGHTLOADINGTOTAL,SE_ADDITIONALCOST,SE_MONTHSHIPACCOUNT,SE_DIFFERENTADJUST,
SE_RATE,SE_MONTHACCOUNT,SE_CREATEUSER,SE_CREATETIME,SE_STATUS,SE_ORGANCODE)
VALUES (
tt.SE_UUIDTT,tt.SE_ACCOUNTCODETT,tt.SE_LOGCODETT,tt.SE_LOGNAMETT,tt.SE_SUPCODETT,tt.SE_SUPNAMETT,tt.SE_ORIGINTT,tt.SE_DESTINATIONTT,
tt.SE_LOGTYPETT,tt.SE_SETTLEMONTHTT,tt.SE_MONTHDELIVERY_MTT,tt.SE_UNITPRICE_MTT,tt.SE_MONTHDELIVERY_TTT,tt.SE_UNITPRICE_TTT,tt.SE_TIHUOTIMESTT,
tt.SE_SONGTIMESTT,tt.SE_TISONGTIMESTT,tt.SE_VOLUMELOADINGTOTALTT,tt.SE_WEIGHTLOADINGTOTALTT,tt.SE_ADDITIONALCOSTTT,tt.SE_MONTHSHIPACCOUNTTT,
tt.SE_DIFFERENTADJUSTTT,tt.SE_RATETT,tt.SE_MONTHACCOUNTTT,tt.SE_CREATEUSERTT,tt.SE_CREATETIMETT,tt.SE_STATUSTT
)
</insert>
------------有的话就更新，没有的话就插入--------
<!-- Nany 0705 am 更新数据 -->
<insert id="UpdateOrInsrt" parameterType="com.gwm.entity.orderManage.VolumeInfoReportEntity">
begin
update TMS_TRANSIT_VOLINFOREPORT
set PICKUPCHARGENUM=#{pickUpChargeNum},DELIVERYNUM=#{deliveryNum},PICKUPDLIVERYNUM=#{pickUpDliveryNum},HANDLINGVOLUME=#{handlingVolune},HANDLINGWEIGHT=#{handlingWeight}
where LTSI_ID=#{ltsi_id};
if sql%notfound then
insert into
TMS_TRANSIT_VOLINFOREPORT (LTSI_ID,PICKUPCHARGENUM,DELIVERYNUM,PICKUPDLIVERYNUM,HANDLINGVOLUME,HANDLINGWEIGHT)
values(#{ltsi_id},#{pickUpChargeNum},#{deliveryNum},#{pickUpDliveryNum},#{handlingVolune},#{handlingWeight}) ;
end if;
end;
</insert>
-------decode----------
select decode(TMS_TRANSITDETAIL.TTD_MATERIALTYPE,0,sum(TMS_TRANSITDETAIL.TTD_DELIVERNUMBER)*MATERIAL_WEIGHT,
sum(TMS_TRANSITDETAIL.TTD_DELIVERNUMBER)*MATERIAL_VOLUME) 实际,
decode(TMS_TRANSITDETAIL.TTD_MATERIALTYPE,0,sum(TMS_TRANSITDETAIL.TTD_PLANDELIVERNUMBER)*MATERIAL_WEIGHT,
sum(TMS_TRANSITDETAIL.TTD_PLANDELIVERNUMBER)*MATERIAL_VOLUME) 计划
from TMS_TRANSITDETAIL
left join TMS_MATERIALINFORMATION on TMS_MATERIALINFORMATION.MATERIAL_CODE=TMS_TRANSITDETAIL.TTD_MATERIALID
where TT_ID in('b88c4694-9019-43c6-93f8-da2b48e19382','2a069872-6fb8-4baa-88e3-c43e527b194c')
GROUP BY TMS_TRANSITDETAIL.TTD_MATERIALTYPE,MATERIAL_WEIGHT,MATERIAL_VOLUME
难道只要出现的字段都需要在group by后面 尽管不需要该字段！！！
-------一共查询9张表，先3张表计算，在和其余6张表合并----------
<select id="chuhuozhishidan" parameterType="com.gwm.entity.settlementNew.ShipTransportationAccountEntity"
resultType="com.gwm.entity.orderManage.ShipmentOrderEntity">
select
DISTINCT
zhishi.LTSI_CODE ltsi_code,
zhishi.LTSI_STATE ltsi_state,
zhishi.OU_CODE ou_code,
zhishi.OU_NAME ou_name,
zhishi.LTSI_DELIVERYDATE ltsi_deliverydate,
zhishi.LTSI_RECEIVENAME ltsi_receivename,
zhishi.LTSI_RECEIVEADRESSNAME ltsi_receiveadressname,
t2.actualCount sumAll,
t2.palnCount planSumAll,
t2.actualWeight sumCount1,
t2.planWeight planSumCount1,
t2.actualVolum sumCount,
t2.planVolum planSumCount,
yundan.LTO_LOGCODE lto_logcode,
yundan.LTO_LOGNAME lto_logname,
caigou.TT_FACTORY factorycode,
yundan.LTO_TRANSPORTTYPE lto_logtype,
tisong.PICKUPCHARGENUM pickUpChargeNum,
tisong.DELIVERYNUM deliveryNum,
tisong.PICKUPDLIVERYNUM pickUpDliveryNum,
tisong.HANDLINGVOLUME handlingVolune,
tisong.HANDLINGWEIGHT handlingWeight,
tisong.REMARKS remarks,
zhishi.LTSI_CREATEUSER ltsi_createuser
from
TMS_LOGN_SHIPMENTINDICTOR zhishi
left join TMS_LONGDISTACE_TRANSPORTORDR yundan on zhishi.LTO_CODE=yundan.LTO_CODE
left join TMS_TRANSIT_VOLINFOREPORT tisong on zhishi.LTSI_ID=tisong.LTSI_ID
left join TMS_CT_LC_PRICECLAUSE hetong on yundan.LTO_LPC_ID=hetong.LPC_ID
left join TMS_TRANSIT caigou on caigou.LTSI_ID=zhishi.LTSI_ID
left join TMS_TRANSITDETAIL mingxi on mingxi.TT_ID=caigou.TT_ID
left join (
select sum(ac) actualCount ,sum(pla) palnCount,
max(case when TTD_MATERIALTYPE=0 then a else 0 end) actualWeight,
max(case when TTD_MATERIALTYPE=0 then b else 0 end) planWeight,
max(case when TTD_MATERIALTYPE=1 then a else 0 end) actualVolum,
max(case when TTD_MATERIALTYPE=1 then a else 0 end) planVolum,
LTSI_ID
from
(
select
sum(TTD_DELIVERNUMBER) ac,sum(TTD_PLANDELIVERNUMBER) pla,
sum(TTD_DELIVERNUMBER)*decode(TTD_MATERIALTYPE,0,TMS_MATERIALINFORMATION.MATERIAL_WEIGHT,TMS_MATERIALINFORMATION.MATERIAL_VOLUME) a,
sum(TTD_PLANDELIVERNUMBER)*decode(TTD_MATERIALTYPE,0,TMS_MATERIALINFORMATION.MATERIAL_WEIGHT,TMS_MATERIALINFORMATION.MATERIAL_VOLUME) b,
TTD_MATERIALTYPE,
LTSI_ID
from TMS_TRANSITDETAIL
left join TMS_TRANSIT on TMS_TRANSITDETAIL.TT_ID=TMS_TRANSIT.TT_ID
left join TMS_MATERIALINFORMATION on TMS_MATERIALINFORMATION.MATERIAL_CODE=TMS_TRANSITDETAIL.TTD_MATERIALID
and TMS_MATERIALINFORMATION.SUPPLIER_CODE=TMS_TRANSIT.TT_SUPCODE
and TMS_MATERIALINFORMATION.MATERIAL_BELONGOUCODE=TMS_TRANSIT.TT_ORGANCODE
GROUP BY TTD_MATERIALTYPE,TMS_MATERIALINFORMATION.MATERIAL_WEIGHT,TMS_MATERIALINFORMATION.MATERIAL_VOLUME,LTSI_ID) t1
GROUP BY LTSI_ID) t2 on t2.LTSI_ID=caigou.LTSI_ID
where zhishi.OU_CODE=#{supcode}
and zhishi.LTSI_DELIVERYDATE like #{settleMonth}
and yundan.LTO_LOGCODE=#{logcode}
and hetong.LPC_BEGIN=#{origin}
and hetong.LPC_END=#{destinationss}
and yundan.LTO_TRANSPORTTYPE=#{logtype}
---------------------where写法------------------
<where>
<trim prefixOverrides="and">
<if test="uuid != null and uuid !=''">
and SE_UUID = #{uuid}
</if>
<if test="logname != null and logname !=''">
and SE_LOGNAME like '%${logname}%'
</if>
</trim>
</where>
---------update---------
<update id="updateByid"
parameterType="com.gwm.entity.settlementNew.ShipTransportationAccountEntity">
update TMS_SETTLEMENT
<set>
<if test="monthDelivery_M != null">
SE_MONTHDELIVERY_M = #{monthDelivery_M},
</if>
<if test="monthDelivery_T != null">
SE_MONTHDELIVERY_T = #{monthDelivery_T},
</if>
</set>
<where>
<trim prefixOverrides="AND">
<if test="ltsi_id != null">
and SE_LTSI_ID = #{ltsi_id}
</if>
</trim>
</where>
</update>
------根据多个条件删除某个对象(将条件封装成map)
@DeleteMapping(value = "/delete/{mesureIds}/{dataItems}")
public boolean delete(@PathVariable("mesureIds") String[] mId, @PathVariable("dataItems") String[] dataItems) {
List<Map> ids = new ArrayList<>();
for (int i = 0; i < mId.length; i++) {
Map map = new HashMap();
map.put("measureId", mId[i]);
map.put("dataItem", dataItems[i]);
ids.add(map);
}
return quotaService.deleteByMesureIdAndDataItem(ids);
}
<delete id="deleteByMesureIdAndDataItem" parameterType="java.util.Map">
DELETE FROM t_alarm_quota
<where>
<foreach collection="list" index="index" item="item" open=""
separator="OR" close="">
(MEASURE_ID=#{item.measureId} AND DATA_ITEM_CODE=#{item.dataItem})
</foreach>
</where>
</delete>
------多表关联且带有where条件-----
select t1.name,t2.grade from t1
left join t2 on t2.class=t1.class (这后面放t2的条件 如 and t2.province='b')
这后面放t1的条件(如 where t1.school='aa')
------mysql排序(技巧性)-------------------
SELECT
id,
Score,
( SELECT count( DISTINCT score ) FROM Scores a WHERE a.score >= s.score ) `rank`
FROM
Scores s
ORDER BY Score DESC;
=========================================================================
=========================================================================
===============================SpringBoot================================
=========================================================================
昨天一controller返回乱码问题,最后把mapper.xml中的返回类型改成<select id="" resultType="java.util.Map">
前台地址(0906):
第一种情况:
@RequestMapping(value = "/statistics")
public R getData(@RequestParam("enterpriseID",required=false) String enterpriseID, 不加这个的话如果enterpriseID不传的话就会报错
@RequestParam("measureID") String measureID,
@RequestParam("ym") String ym,
HttpServletResponse response) {
第二种情况:
@RequestMapping(value = "/statistics/{enterpriseID}/{measureID}/{ym}")
public R getData(@PathVariable("enterpriseID") String enterpriseID,
@PathVariable("measureID") String measureID,
@PathVariable("ym") String ym,
HttpServletResponse response) {
-------------------------------------------------------------------------------------------------
/**
* 根据查询条件查询符合条件的记录
*
* @param paramsMap
* @return
*/
@GetMapping("/listByMap")
public List<Quota> listByMap(@RequestParam Map paramsMap) { 这个map是前台传过来的
List<Quota> list = quotaService.selectByMap(paramsMap);
dictUtil.listconvert(list, dictcf);
return list;
}
-------------------------------------------------------------------------------------------------
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
