主页 / coding / 问题 / 77589562
Accepted
user23025456
Asked: 2023-12-02 15:57:31 +0800 CST

我可以在 FULL OUTER JOIN 期间避免 COALESCE 吗？

  • 772

在比较 2 个表之间的数据的查询中，我经常使用 和 的组合COALESCE来FULL OUTER JOIN显示仅在其中 1 个表中可用的记录。

这可以用更少的语法糖来完成吗？（我的意思并不是替换COALESCE为之NVL类的。）

WITH Dataset1 AS (
    SELECT
        id,
        SUM(amount) AS amount
    FROM
        table1
    GROUP BY
        id
),

Dataset2 AS (
    SELECT
        id,
        SUM(amount) AS amount
    FROM
        table2
    GROUP BY
        id
)

SELECT
    COALESCE(d1.id, d2.id) AS ID,
    COALESCE(d1.amount, 0) AS D1_AMOUNT,
    COALESCE(d2.amount, 0) AS D2_AMOUNT,
    COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0) AS DELTA
FROM
    Dataset1 d1
FULL OUTER JOIN
    Dataset2 d2 c ON
        d2.id = d1.id
WHERE
    ABS(COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0)) >= 5
ORDER BY
    ID
sql

2 个回答

  1. Best Answer

    您编写查询的方式是正确的方式，并且COALESCE对于它正常工作是必要的，因为在完整外连接中任何列都可以为空。

    不过，我会养成一种习惯，以不同于列的方式命名操作结果。在这里，您处理名为 的列amount并将结果命名为amount：

    SUM(amount) AS amount

    我宁愿做这个

    SUM(amount) AS total

    至于 ID：您可以使用USING而不是ON避免COALESCE在连接的列上使用。USING对于完全外连接查询来说是非常典型的，并且当在同一列上外连接多个表时变得更加方便。

    WITH
  dataset1 AS (SELECT id, SUM(amount) AS total FROM table1 GROUP BY id),
  dataset2 AS (SELECT id, SUM(amount) AS total FROM table2 GROUP BY id)
SELECT
    id,
    COALESCE(d1.total, 0) AS d1_amount,
    COALESCE(d2.total, 0) AS d2_amount,
    COALESCE(d1.total, 0) - COALESCE(d2.total, 0) AS delta
FROM dataset1 d1
FULL OUTER JOIN dataset2 d2 USING (id)
WHERE ABS(COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0)) >= 5
ORDER BY id;
    • 1

  2. 我不知道这样是否更好。

    WITH DataSet(ID, D1_AMOUNT, D2_AMOUNT, DELTA) AS (
    SELECT ID, SUM(COALESCE(Amount,0)), 0, SUM(COALESCE(Amount,0))
    FROM table1
    GROUP BY ID
    UNION ALL
    SELECT ID, 0, SUM(COALESCE(Amount,0)), SUM(COALESCE(Amount,0))*-1
    FROM table2
    GROUP BY ID
),
DataSet2(ID, D1_AMOUNT, D2_AMOUNT, DELTA) AS (
    SELECT ID,SUM(D1_AMOUNT),SUM(D2_AMOUNT), SUM(DELTA) 
    FROM DataSet 
    GROUP BY ID
)
SELECT *
FROM Dataset2
WHERE ABS(DELTA)>5
    • 0

相关问题