对账sql

最新推荐文章于 2025-03-18 15:00:00 发布

转载最新推荐文章于 2025-03-18 15:00:00 发布 · 466 阅读

2 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/liuyifan/p/5060461.html

文章标签：

#数据库

本文提供了一个复杂的SQL查询案例，展示了如何通过左连接和右连接合并两个表，并使用UNION操作符来获取非匹配记录。该查询从trade表和wxw表中选取了特定类型的订单数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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