游标:行中保留最大整数值，前一行中的其他值变为零

-- 创建示例表
CREATE TABLE data_table (
    id INT PRIMARY KEY,
    value INT
);

-- 插入示例数据
INSERT INTO data_table (id, value) VALUES (1, 10);
INSERT INTO data_table (id, value) VALUES (2, 20);
INSERT INTO data_table (id, value) VALUES (3, 30);

-- 使用游标处理数据
DECLARE @max_value INT;
DECLARE @current_id INT;
DECLARE @current_value INT;

-- 声明游标
DECLARE cur CURSOR FOR
SELECT id, value FROM data_table ORDER BY id;

-- 打开游标
OPEN cur;

-- 获取第一行数据
FETCH NEXT FROM cur INTO @current_id, @current_value;

-- 处理每一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @current_value > @max_value THEN
        SET @max_value = @current_value;
    ELSE
        UPDATE data_table SET value = 0 WHERE id = @current_id;
    END IF;

    -- 获取下一行数据
    FETCH NEXT FROM cur INTO @current_id, @current_value;
END;

-- 关闭游标
CLOSE cur;
DEALLOCATE cur;

-- 查询处理后的数据
SELECT * FROM data_table;