使用while循环计算两行的差值并将结果插入到临时表中

CREATE TABLE temp_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    difference DECIMAL(10, 2)
);

-- 假设数据表名为data_table，字段名为value
SELECT value
FROM data_table
ORDER BY id;

DECLARE @previous DECIMAL(10, 2);
SET @previous = NULL;

DECLARE @current DECIMAL(10, 2);

DECLARE @difference DECIMAL(10, 2);

DECLARE @id INT;
SET @id = 0;

WHILE @id IS NOT NULL
BEGIN
    SELECT @id = id, @current = value
    FROM data_table
    WHERE id > @id
    ORDER BY id
    LIMIT 1;

    IF @id IS NOT NULL
    BEGIN
        IF @previous IS NOT NULL
        BEGIN
            SET @difference = @current - @previous;
            INSERT INTO temp_table (difference) VALUES (@difference);
        END;

        SET @previous = @current;
    END;
END;

-- 查询临时表的结果
SELECT * FROM temp_table;