UINION ALL关联查询
需要用到两张表的累计数据，且字段相同，使用UINION ALL，
<select id="getOrderTrackingBoardInfo" resultMap="OrderTrackingBoardInfoMap">
<if test="labelPage == null or labelPage == 'TO_SUBMITTED'">
SELECT
a.*
FROM
(
SELECT
"TrackingBoard" LABEL_PAGE,
dmspo.ORDER_NO,
dmspo.PART_ORDER_TYPE ORDER_TYPE,
'待提报' ORDER_STATUS_NAME,
DATE_FORMAT( dmspo.ORDER_DATE, '%Y-%m-%d %H:%i:%s' ) ORDER_SUBMIT_DATE,
NULL AS ORDER_SUBMIT_USER,
dmspo.REMARK ORDER_REMARK,
dmspo.ORDER_STATUS ORDER_STATUS,
stdra.DELIVERER,
stdra.DELIVERER_PHONE_NUMBER DELIVERER_MOBILE,
stdra.DELIVERER_ADDRESS,
dmspoi.ITEM_ID ITEM_ID,
dmspoi.PART_NO PART_CODE,
dmspoi.PART_NAME,
dmspo.VIN,
10081000 AS ITEM_STATUS,-- '待提报' AS ITEM_STATUS_CN,
0 ADD_SIGNED_QUANTITY,
0 ADD_SUBMITTED_QUANTITY,
0 AS QUANTITY,
dmspoi.COUNT TO_SUBMITTED_QUANTITY,
0 AS SIGNED_QUANTITY,
0 AS ON_PASSAGE_QUANTITY,
0 AS TO_DELIVERY_QUANTITY,
0 AS DELAYED_QUANTITY,
0 AS TO_PROCESS_QUANTITY,
0 AS SUBMITTED_QUANTITY,
0 AS REJECTED_QUANTITY,
NULL AS REJECTED_REASON,
NULL AS DELAY_REMARK
FROM
tt_pt_dms_order dmspo
LEFT JOIN tt_pt_dms_order_item dmspoi ON dmspo.ORDER_NO = dmspoi.ORDER_NO
LEFT JOIN spms_tm_dealer_receiving_address stdra ON stdra.DELIVERER_ADDRESS_CODE = dmspo.DELIVERER_ADDRESS_CODE
WHERE
dmspo.ORDER_STATUS = '80051001'
<if test="searchInfo != null and searchInfo != '' ">
AND (dmspo.ORDER_NO LIKE CONCAT('%',#{searchInfo},'%') OR dmspoi.PART_NO LIKE CONCAT('%',#{searchInfo},'%') OR dmspoi.PART_NAME LIKE CONCAT('%',#{searchInfo},'%') OR dmspo.VIN LIKE CONCAT('%',#{searchInfo},'%'))
</if>
<if test="orderNo != null and orderNo != '' ">
AND dmspo.ORDER_NO LIKE CONCAT('%',#{orderNo},'%')
</if>
<if test="orderTypes != null and orderTypes != '' ">
AND dmspo.PART_ORDER_TYPE in
<foreach collection="orderTypes" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="partCode != null and partCode !='' ">
AND dmspoi.PART_NO LIKE CONCAT('%',#{partCode},'%')
</if>
<if test="partName != null and partName !='' ">
AND dmspoi.PART_NAME LIKE CONCAT('%',#{partName},'%')
</if>
<if test="startOrderSubmitDate != null and startOrderSubmitDate != '' ">
AND DATE(dmspo.ORDER_DATE)
<![CDATA[ >= ]]> #{startOrderSubmitDate}
</if>
<if test="endOrderSubmitDate != null and endOrderSubmitDate != '' ">
AND DATE(dmspo.ORDER_DATE)
<![CDATA[ <= ]]> #{endOrderSubmitDate}
</if>
ORDER BY
dmspo.ORDER_DATE
) a
</if>
<if test="labelPage == null">
UNION ALL
</if>
<if test="(labelPage == null or labelPage=='REJECTED' or labelPage=='COMPLETED' or labelPage=='ONGOING')">
SELECT
b.*
FROM
(
SELECT
"TrackingBoard" LABEL_PAGE,
po.ORDER_NO,
po.ORDER_TYPE,
CASE
WHEN tpdo.ORDER_STATUS = 80051002 THEN
'已驳回'
WHEN ADD_SIGNED_QUANTITY = ADD_SUBMITTED_QUANTITY THEN
'已完成' ELSE '进行中'
END ORDER_STATUS_NAME,
DATE_FORMAT( po.ORDER_SUBMIT_DATE, '%Y-%m-%d %H:%i:%s' ) ORDER_SUBMIT_DATE,
po.ORDER_SUBMIT_USER,
po.REMARK ORDER_REMARK,
po.ORDER_STATUS,
po.DELIVERER,
po.DELIVERER_MOBILE,
po.DELIVERER_ADDRESS,
poi.ID ITEM_ID,
poi.PART_CODE,
poi.PART_NAME,
po.VIN,
poi.ITEM_STATUS,
ADD_COUNT_PO.ADD_SIGNED_QUANTITY,
ADD_COUNT_PO.ADD_SUBMITTED_QUANTITY,
poi.QUANTITY,
0 AS TO_SUBMITTED_QUANTITY,-- 待提报数量
IFNULL( psv.SIGNED_QUANTITY, 0 ) SIGNED_QUANTITY,-- 已签收数量
IFNULL( popv.ON_PASSAGE_QUANTITY, 0 ) ON_PASSAGE_QUANTITY,-- 在途数量
IFNULL( ptdv.TO_DELIVERY_QUANTITY, 0 ) TO_DELIVERY_QUANTITY,-- 待发货数量
IFNULL( pdv.DELAYED_QUANTITY, 0 ) DELAYED_QUANTITY,-- 已延期数量
IFNULL( ptpv.TO_PROCESS_QUANTITY, 0 ) TO_PROCESS_QUANTITY,-- 待处理数量
IFNULL( psbv.SUBMITTED_QUANTITY, 0 ) SUBMITTED_QUANTITY,-- 已提报数量
IFNULL( prv.REJECTED_QUANTITY, 0 ) REJECTED_QUANTITY,-- 已驳回数量
IFNULL( prv.REJECTED_REASON, '' ) REJECTED_REASON,
IFNULL( pdv.DELAY_REMARK, '' ) DELAY_REMARK
FROM
spms_tt_part_purchase_order po
LEFT JOIN tt_pt_dms_order tpdo ON tpdo.ORDER_NO = po.ORDER_NO
LEFT JOIN spms_tt_part_purchase_order_item poi ON poi.PURCHASE_ID = po.ID
LEFT JOIN part_signed_view psv ON psv.item_id = poi.id
AND psv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_on_passage_view popv ON popv.item_id = poi.id
AND popv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_to_delivery_view ptdv ON ptdv.item_id = poi.id
AND ptdv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_delayed_view pdv ON pdv.item_id = poi.id
AND pdv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_to_process_view ptpv ON ptpv.item_id = poi.id
AND ptpv.ORDER_NO = po.ORDER_NO
LEFT JOIN part_submitted_view psbv ON psbv.item_id = poi.id
AND psbv.ORDER_NO = po.ORDER_NO
LEFT JOIN part_rejected_view prv ON prv.item_id = poi.id
AND prv.ORDER_NO = po.ORDER_NO
LEFT JOIN (
SELECT
po.ORDER_NO,
SUM( IFNULL( psv.SIGNED_QUANTITY, 0 ) ) ADD_SIGNED_QUANTITY,
SUM( IFNULL( psbv.SUBMITTED_QUANTITY, 0 ) ) ADD_SUBMITTED_QUANTITY
FROM
spms_tt_part_purchase_order po
LEFT JOIN tt_pt_dms_order tpdo ON tpdo.ORDER_NO = po.ORDER_NO
LEFT JOIN spms_tt_part_purchase_order_item poi ON poi.PURCHASE_ID = po.ID
LEFT JOIN part_signed_view psv ON psv.item_id = poi.id
AND psv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_on_passage_view popv ON popv.item_id = poi.id
AND popv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_to_delivery_view ptdv ON ptdv.item_id = poi.id
AND ptdv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_delayed_view pdv ON pdv.item_id = poi.id
AND pdv.ORDER_NO = po.ORDER_NO
AND po.ORDER_STATUS = '10041005'
LEFT JOIN part_to_process_view ptpv ON ptpv.item_id = poi.id
AND ptpv.ORDER_NO = po.ORDER_NO
LEFT JOIN part_submitted_view psbv ON psbv.item_id = poi.id
AND psbv.ORDER_NO = po.ORDER_NO
LEFT JOIN part_rejected_view prv ON prv.item_id = poi.id
AND prv.ORDER_NO = po.ORDER_NO
GROUP BY
po.ORDER_NO
) ADD_COUNT_PO ON ADD_COUNT_PO.ORDER_NO = po.ORDER_NO
WHERE 1=1
<if test="searchInfo != null and searchInfo != '' ">
AND (po.ORDER_NO LIKE CONCAT('%',#{searchInfo},'%') OR poi.PART_CODE LIKE CONCAT('%',#{searchInfo},'%') OR poi.PART_NAME LIKE CONCAT('%',#{searchInfo},'%') OR po.VIN LIKE CONCAT('%',#{searchInfo},'%'))
</if>
<if test="orderNo != null and orderNo != '' ">
AND po.ORDER_NO LIKE CONCAT('%',#{orderNo},'%')
</if>
<if test="orderTypes != null and orderTypes != '' ">
AND po.ORDER_TYPE in
<foreach collection="orderTypes" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="partCode != null and partCode !='' ">
AND poi.PART_CODE LIKE CONCAT('%',#{partCode},'%')
</if>
<if test="partName != null and partName !='' ">
AND poi.PART_NAME LIKE CONCAT('%',#{partName},'%')
</if>
<if test="startOrderSubmitDate != null and startOrderSubmitDate != '' ">
AND DATE(po.ORDER_SUBMIT_DATE)
<![CDATA[ >= ]]> #{startOrderSubmitDate}
</if>
<if test="endOrderSubmitDate != null and endOrderSubmitDate != '' ">
AND DATE(po.ORDER_SUBMIT_DATE)
<![CDATA[ <= ]]> #{endOrderSubmitDate}
</if>
<if test="labelPage=='REJECTED'">
AND tpdo.ORDER_STATUS = 80051002
</if>
<if test="labelPage=='COMPLETED'">
AND ADD_SIGNED_QUANTITY = ADD_SUBMITTED_QUANTITY
</if>
<if test="labelPage=='ONGOING'">
AND tpdo.ORDER_STATUS != 80051002 AND ADD_SIGNED_QUANTITY-ADD_SUBMITTED_QUANTITY != 0
</if>
ORDER BY
po.ORDER_SUBMIT_DATE
) b
</if>
LIMIT ${offset}, ${limit}
</select>
其中想要统计子表的字段值相加数量，又不能使用sum()，此时使用了子表（先关联想要统计个数的相关表，然后使用GROUP BY，来统计对应主表的信息），此时还有一个问题，就是 mybatis中标签中加入 的用法及一对多查询limit数量不对LIMIT查询出来的数量是子表的数量，并不符合业务逻辑，想要解决此问题，可以在where里加一个子表，在子表使用limit关键字，(where po.id in (子表.id limit 0,10))
