主页 / dba / 问题 / 265713
Accepted
Nick Buxton
Asked: 2020-04-24 11:46:49 +0800 CST

如何加入时间戳并包含 NULL 条目？

  • 772

我正在尝试查询以将每日会计发票的总和与每日预算进行比较。预算存储为一个月的一个值，因此我将该金额除以总天数，因为 4 月份将有 30 个条目。

但是，即使当天没有发票，我也希望这些天的预算金额永久存在，无论是当天还是实际上当天。

我当前的查询加入了时间戳的 date_part('day',a.date) ，该时间戳仅在当天有发票时才会出现。我正在使用 PostgreSQL 版本 11.3。我已经尝试了所有加入的可能性，并且每次都得到相同的结果。除非我使用 CROSS JOIN，它确实给出了所有 30 天，但每天所有发票的总和。我曾认为 FULL OUTER JOIN 会起作用，或者以其他方式使用 UNION ALL，但到目前为止我还没有成功。

WITH RECURSIVE t(days,budget) AS (
SELECT 1 as days,b.amount/30 AS budget
FROM budgets b
WHERE b.id = 10
UNION ALL
SELECT t.days + 1,t.budget
FROM t WHERE t.days < 30)
SELECT
t.days AS "Day",
COALESCE ( SUM ( CASE WHEN a.account_id IN (17,43,44)
THEN a.credit - a.debit ELSE 0 END),0) AS "Invoices",
t.budget AS "Budget"
FROM t
LEFT JOIN accounting a
ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.account_id = 20
GROUP BY 1,3;

对于输出，假设第 1 天、第 2 天和第 3 天已经过去，而第 2 天没有条目。
当前产出
日发票预算
1 100 50
3 75 50

期望产出
日发票预算
1 100 50
2 0 50
3 75 50
4 0 50
5 0 50
...
30 0 50

我愿意接受其他查询想法来实现这一点。感谢您的帮助！

union datetime

1 个回答

  1. Best Answer

    我已经设法做到了这一点，方法是从主查询中完全删除连接并创建一个 CASE WHEN 子查询，如果匹配则首先查看，否则分配 0。然后，对符合条件的发票进行汇总。

    如果有比我现在下面的查询更好的方法来处理这个问题，请告诉我。由于我正在使用 CASE WHEN 和其中的子查询，因此我认为这部分可以以某种方式更好地简化。但是，如果它有效，它就有效并且看起来并不昂贵。

    另外，请注意：上面我错误地引用了a.account_id第 18 行（倒数第二个）。当我将在我的情况下使用的确切表修改为更简单的集合以理解所使用的 SQL 时，就会发生这种情况。确保我的会计表表示中的字段：

    a.account_id= 代表会计科目表的
    a.invoice_idID = 代表发票上销售组的 ID（上面的错误a.account_id是 20，应改为a.invoice_id= 20）
    a.credit= 双账会计贷项
    a.debit= 双账会计借方

    WITH RECURSIVE t(days,budget) AS (
    SELECT 1 as days,b.amount/30 AS budget
    FROM budgets b
    WHERE b.id = 10
    UNION ALL
    SELECT t.days + 1,t.budget
    FROM t WHERE t.days < 30)
    SELECT
    t.days AS "Day",
    SUM ( CASE WHEN
    ( SELECT a.date FROM accounting a
    LEFT JOIN t ON date_part('day',a.date) = t.days
    WHERE a.date >= '2020-04-01'
    AND a.date <= '2020-04-30'
    AND a.invoice_id = 20
    LIMIT 1) IS NULL
    THEN 0 ELSE
    ( SELECT SUM
    ( CASE WHEN a.account_id IN (17,43,44)
    THEN a.credit - a.debit ELSE 0 END)
    FROM accounting a
    WHERE date_part('day',a.date) = t.days
    AND a.date >= '2020-04-01'
    AND a.date <= '2020-04-30'
    AND a.invoice_id = 20)
    END ) AS "Invoices",
    t.budget AS "Budget"
    FROM t
    GROUP BY 1,3
    ORDER BY 1;

    我感谢那些看过这个查询并考虑解决它的方法的人。

    • 0

相关问题