SQLite中cash与bank表联合查询：分设现金/银行余额列并按日期排序

Got it, let's work through this problem step by step. You've already got a UNION query to combine transactions from your cash and bank tables, and now you need to add two dedicated columns: cash_balance and bank_balance where only the relevant balance updates based on which table the record originates from.

First, let's clarify the core requirement:

• For records from the cash table: cash_balance should use the balance from that transaction, while bank_balance stays as the latest bank account balance for the same person up to that transaction date.
• For records from the bank table: bank_balance uses the transaction's balance, and cash_balance reflects the latest cash account balance for that person up to that date.

Here's a robust implementation using a CTE (Common Table Expression) and correlated subqueries to fetch the appropriate historical balances:

WITH combined_transactions AS (
    -- Handle cash transactions: pull cash balance from the record, get latest bank balance up to this date
    SELECT
        id,
        person_id,
        date,
        remarks,
        type,
        amount,
        balance AS cash_balance,
        (
            SELECT balance
            FROM bank b
            WHERE b.person_id = c.person_id
              AND b.date <= c.date
            ORDER BY b.date DESC, b.id DESC -- Use ID to break ties if multiple transactions share the same date
            LIMIT 1
        ) AS bank_balance
    FROM cash c

    UNION ALL

    -- Handle bank transactions: pull bank balance from the record, get latest cash balance up to this date
    SELECT
        id,
        person_id,
        date,
        remarks,
        type,
        amount,
        (
            SELECT balance
            FROM cash c
            WHERE c.person_id = b.person_id
              AND c.date <= b.date
            ORDER BY c.date DESC, c.id DESC
            LIMIT 1
        ) AS cash_balance,
        balance AS bank_balance
    FROM bank b
)
-- Final sorted output
SELECT
    id,
    person_id,
    date,
    remarks,
    type,
    amount,
    cash_balance,
    bank_balance
FROM combined_transactions
ORDER BY date;

Quick notes on this solution: #

• We use UNION ALL instead of plain UNION because UNION removes duplicates, which you likely don't want unless there are identical transactions in both tables.
• The subqueries ensure we're pulling the most recent relevant balance from the other account, tied to the transaction's date. Adding b.id DESC/c.id DESC handles edge cases where multiple transactions happen on the same day.
• If you want to show the current/latest balance of the other account (ignoring the transaction date), just remove the b.date <= c.date condition from the subqueries.

If your needs are simpler—you just want to display the balance from the source table and leave the other balance as NULL—you can use this stripped-down version:

SELECT
    id,
    person_id,
    date,
    remarks,
    type,
    amount,
    balance AS cash_balance,
    NULL AS bank_balance
FROM cash

UNION ALL

SELECT
    id,
    person_id,
    date,
    remarks,
    type,
    amount,
    NULL AS cash_balance,
    balance AS bank_balance
FROM bank

ORDER BY date;

This will show the cash balance only for cash transactions, and bank balance only for bank transactions, with the unused column returning NULL.

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