SQL 行与行差值

WITH numbered_table AS (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
  FROM your_table
)

SELECT current.row_num, 
       current.some_column - previous.some_column AS diff
FROM numbered_table AS current
JOIN numbered_table AS previous
ON current.row_num = previous.row_num + 1

SELECT some_column,
       LAG(some_column) OVER (ORDER BY some_column) AS prev_value
FROM your_table

SELECT some_column,
       some_column - prev_value AS diff
FROM (
  SELECT some_column,
         LAG(some_column) OVER (ORDER BY some_column) AS prev_value
  FROM your_table
) AS subquery

-- 创建示例表orders
CREATE TABLE orders (
  order_id INT,
  amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO orders (order_id, amount) VALUES (1, 100.00);
INSERT INTO orders (order_id, amount) VALUES (2, 150.00);
INSERT INTO orders (order_id, amount) VALUES (3, 200.00);
INSERT INTO orders (order_id, amount) VALUES (4, 250.00);

-- 计算每个订单与上一个订单之间的金额差值
WITH numbered_orders AS (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY order_id) AS row_num
  FROM orders
)
SELECT current.order_id, 
       current.amount - previous.amount AS amount_diff
FROM numbered_orders AS current
JOIN numbered_orders AS previous
ON current.row_num = previous.row_num + 1;

order_id | amount_diff
---------|------------
2        | 50.00
3        | 50.00
4        | 50.00