在mysql中使用循环从两个表中选择数据

-- 创建两个表
CREATE TABLE table1 (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE table2 (
  id INT PRIMARY KEY,
  value INT
);

-- 插入数据
INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table1 (id, name) VALUES (2, 'Jane');
INSERT INTO table1 (id, name) VALUES (3, 'Alice');

INSERT INTO table2 (id, value) VALUES (1, 10);
INSERT INTO table2 (id, value) VALUES (2, 20);
INSERT INTO table2 (id, value) VALUES (3, 30);

-- 使用循环和JOIN语句选择数据
DECLARE i INT DEFAULT 1;
DECLARE max_id INT;
SELECT MAX(id) INTO max_id FROM table1;

WHILE i <= max_id DO
  SELECT table1.name, table2.value
  FROM table1
  JOIN table2 ON table1.id = table2.id
  WHERE table1.id = i;

  SET i = i + 1;
END WHILE;