对账sql

 

 

SELECT
    *
FROM
    (
        SELECT
            t.id 't_id',
            t.order_no 't_order_no',
            t.amount 't_amount',
            t.type 't_type',
            w.id 'w_id',
            w.order_no 'w_order_no',
            w.amount 'w_amount'
        FROM
            (
                SELECT
                    tr.id,
                    tr.order_no,
                    tr.amount,
                    tr.type
                FROM
                    trade tr
                WHERE
                    tr.type = 1
            ) t
        LEFT JOIN wx w ON t.order_no = w.order_no
        AND t.amount = w.amount
        UNION
            SELECT
                t.id 't_id',
                t.order_no 't_order_no',
                t.amount 't_amount',
                t.type 't_type',
                w.id 'w_id',
                w.order_no 'w_order_no',
                w.amount 'w_amount'
            FROM
                (
                    SELECT
                        tr.id,
                        tr.order_no,
                        tr.amount,
                        tr.type
                    FROM
                        trade tr
                    WHERE
                        tr.type = 1
                ) t
            RIGHT JOIN wx w ON t.order_no = w.order_no
            AND t.amount = w.amount
    ) u
WHERE
    u.t_id IS NULL
OR u.w_id IS NULL

 

 

转载于:https://www.cnblogs.com/liuyifan/p/5060461.html