如何编写SQL查询生成table表的Daily Change日余额差值列？

First, let's recap your table structure and sample data to make sure we're aligned:

Your goal is to compute Daily Change as the difference between the current day's Balance and the prior day's Balance (e.g., 3/28's balance minus 3/27's gives 7-8=-1, which matches your expected result).

Solution Using Window Functions #

The most efficient and clean way to do this in modern SQL is with the LAG() window function—no messy self-joins required. Here's the query:

SELECT
    Balance,
    `Value 1`,
    `Value2`,
    Date,
    Balance - LAG(Balance, 1) OVER (ORDER BY STR_TO_DATE(Date, '%m/%d/%Y')) AS `Daily Change`
FROM
    `table`
ORDER BY
    STR_TO_DATE(Date, '%m/%d/%Y');

Query Breakdown: #

• LAG(Balance, 1): This pulls the Balance value from the immediately preceding row relative to the current one.
• OVER (ORDER BY STR_TO_DATE(Date, '%m/%d/%Y')): We sort rows chronologically by converting the string Date to a proper date type (critical to avoid sorting errors like 10/1 coming before 3/27). If your Date column is already a date/time data type, simplify this to ORDER BY Date.
• Balance - LAG(...): Subtracts the prior day's balance from the current day's to get the daily change.

Quick Adjustments: #

• For the first row (3/27/2018), there's no prior day data, so Daily Change will return NULL. If you want to set this to 0 instead, use COALESCE():

  Balance - COALESCE(LAG(Balance, 1) OVER (ORDER BY STR_TO_DATE(Date, '%m/%d/%Y')), Balance) AS `Daily Change`
  

  This makes the first day's change 0 by subtracting the balance from itself.

Sample Output: #

Running the base query on your data gives:

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