Laravel按日期关联表并汇总金额实现收支余额展示

没问题，这个需求我帮你梳理清楚，咱们一步步来实现按日期汇总收支、处理无记录日期并计算余额的功能：

核心思路 #

要实现你的需求，关键要解决两个核心问题：

• 覆盖所有需要展示的日期（包括没有收入/支出记录的日期）
• 对每日的收支进行汇总，没有记录的日期用0填充，最后计算当日余额

拆解下来分四步执行：

1. 生成连续的日期范围（覆盖所有有收支记录的日期，或者你指定的固定范围）
2. 分别对Income和Expenses表按日期汇总收入、支出金额
3. 将日期范围表和两个汇总表做左连接，把无记录的日期的收支金额替换为0
4. 计算当日余额（当日收入 - 当日支出）

具体SQL实现 #

下面针对主流数据库给出具体代码：

方案1：PostgreSQL #

PostgreSQL内置了generate_series函数，生成连续日期非常方便：

-- 1. 生成所有涉及的日期范围（取收支表中最早到最晚的日期）
WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(Date) FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates),
        (SELECT MAX(Date) FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates),
        '1 day'::interval
    ) AS Date
),
-- 2. 按日期汇总每日收入
daily_income AS (
    SELECT Date, COALESCE(SUM(IncomeAmount), 0) AS IncomeAmount
    FROM Income
    GROUP BY Date
),
-- 3. 按日期汇总每日支出
daily_expenses AS (
    SELECT Date, COALESCE(SUM(ExpenseAmount), 0) AS ExpenseAmount
    FROM Expenses
    GROUP BY Date
)
-- 4. 左连接所有表，计算当日余额
SELECT
    dr.Date::DATE,
    COALESCE(di.IncomeAmount, 0) AS IncomeAmount,
    COALESCE(de.ExpenseAmount, 0) AS ExpenseAmount,
    COALESCE(di.IncomeAmount, 0) - COALESCE(de.ExpenseAmount, 0) AS Balance
FROM date_range dr
LEFT JOIN daily_income di ON dr.Date::DATE = di.Date
LEFT JOIN daily_expenses de ON dr.Date::DATE = de.Date
ORDER BY dr.Date;

方案2：MySQL 8.0+（支持CTE和递归） #

MySQL8.0及以上版本支持递归CTE，可以用它生成连续日期：

-- 1. 生成所有涉及的日期范围
WITH RECURSIVE date_range AS (
    SELECT MIN(Date) AS Date
    FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates
    UNION ALL
    SELECT DATE_ADD(Date, INTERVAL 1 DAY)
    FROM date_range
    WHERE Date < (SELECT MAX(Date) FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates)
),
-- 2. 按日期汇总每日收入
daily_income AS (
    SELECT Date, COALESCE(SUM(IncomeAmount), 0) AS IncomeAmount
    FROM Income
    GROUP BY Date
),
-- 3. 按日期汇总每日支出
daily_expenses AS (
    SELECT Date, COALESCE(SUM(ExpenseAmount), 0) AS ExpenseAmount
    FROM Expenses
    GROUP BY Date
)
-- 4. 左连接并计算当日余额
SELECT
    dr.Date,
    COALESCE(di.IncomeAmount, 0) AS IncomeAmount,
    COALESCE(de.ExpenseAmount, 0) AS ExpenseAmount,
    COALESCE(di.IncomeAmount, 0) - COALESCE(de.ExpenseAmount, 0) AS Balance
FROM date_range dr
LEFT JOIN daily_income di ON dr.Date = di.Date
LEFT JOIN daily_expenses de ON dr.Date = de.Date
ORDER BY dr.Date;

方案3：MySQL 5.x（不支持CTE） #

如果用的是老版本MySQL，可以通过数字辅助表生成日期范围：

-- 先创建一个数字辅助表（如果没有的话）
CREATE TABLE IF NOT EXISTS numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- 生成日期范围并关联汇总数据
SELECT
    dr.Date,
    COALESCE(di.IncomeAmount, 0) AS IncomeAmount,
    COALESCE(de.ExpenseAmount, 0) AS ExpenseAmount,
    COALESCE(di.IncomeAmount, 0) - COALESCE(de.ExpenseAmount, 0) AS Balance
FROM (
    -- 生成从最早到最晚的连续日期
    SELECT 
        DATE_ADD(
            (SELECT MIN(Date) FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates),
            INTERVAL n.n1 + n.n2*10 + n.n3*100 DAY
        ) AS Date
    FROM (SELECT n AS n1 FROM numbers) n
    CROSS JOIN (SELECT n AS n2 FROM numbers) n2
    CROSS JOIN (SELECT n AS n3 FROM numbers) n3
    HAVING Date <= (SELECT MAX(Date) FROM (SELECT Date FROM Income UNION SELECT Date FROM Expenses) AS all_dates)
) dr
LEFT JOIN (
    SELECT Date, COALESCE(SUM(IncomeAmount), 0) AS IncomeAmount
    FROM Income
    GROUP BY Date
) di ON dr.Date = di.Date
LEFT JOIN (
    SELECT Date, COALESCE(SUM(ExpenseAmount), 0) AS ExpenseAmount
    FROM Expenses
    GROUP BY Date
) de ON dr.Date = de.Date
ORDER BY dr.Date;

关键细节说明 #

• COALESCE函数：用来把左连接后出现的NULL值替换为0，确保没有收入/支出的日期显示0而不是空值
• 日期范围控制：如果需要固定日期范围（比如当月所有日期），可以把MIN(Date)和MAX(Date)替换成具体日期，比如'2024-01-01'和'2024-01-31'
• 累计余额扩展：如果需要计算累计余额（从第一天开始累加每日余额），可以用窗口函数，比如在PostgreSQL/MySQL8.0+中添加：

  SUM(COALESCE(di.IncomeAmount,0)-COALESCE(de.ExpenseAmount,0)) OVER (ORDER BY dr.Date) AS CumulativeBalance
  

内容的提问来源于stack exchange，提问作者AugustoM