互联网金融对账思路

SELECT CONCAT_WS('|', order_id,outer_order_id,chan_id,chan_user_id,product_id,order_type,amount,DATE_FORMAT( create_at,'%Y-%m-%d %H:%i:%s'))
FROM order_t WHERE order_status = 'success' AND chan_id = ?1 AND create_at >= ?2 AND create_at < ?3

SELECT t.order_id FROM order_t t LEFT JOIN verification_order v ON t.chan_id = ?1 AND t.outer_order_id = v.order_id
WHERE v.order_id IS NULL AND t.create_at >= ?2 AND t.create_at < ?3

SELECT v.order_id FROM verification_order v LEFT JOIN order_t t ON t.chan_id = ?1 AND v.outer_order_id = t.order_id
WHERE t.order_id IS NULL AND v.create_at >= ?2 AND v.create_at < ?3

SELECT t.order_id FROM order_t t JOIN verification_order v ON t.chan_id = ?1 AND t.outer_order_id = v.order_id
WHERE CONCAT_WS('|',t.chan_id,t.chan_user_id,t.product_id,t.order_type,t.amount,
DATE_FORMAT( t.create_at,'%Y-%m-%d %H:%i:%s'))
!= CONCAT_WS('|',v.chan_id,v.chan_user_id,v.product_id,v.order_type,v.amount,
( v.create_at,'%Y-%m-%d %H:%i:%s')) AND t.create_at >= ?2 AND t.create_at < ?3