表A,表名:testtable,表数据如下:
表B,表名:addtable,表数据如下:
循环查询并记录,存储过程如下:
DELIMITER $$
USE `risecom`$$
DROP PROCEDURE IF EXISTS `失败位置`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `失败位置`(IN k INT , IN m INT)
BEGIN
DECLARE i INT DEFAULT 1;#查询表开始位置
DECLARE j INT DEFAULT 1;#插入表开始位置
WHILE(j<=m) DO #循环插入更新数据
WHILE(i<=k) DO #循环查询表格中是否有‘男’
UPDATE addtable SET 备注 =(
SELECT * FROM (
SELECT CONCAT_WS(
',',(SELECT 备注 FROM addtable WHERE id = j),(SELECT IF((SELECT sex FROM testtable WHERE id = i AND age =21)='男',i,0) AS 查询)
) AS 别名 FROM testtable WHERE id = i
) AS t
) WHERE id = j;
SET i= i + 1;
END WHILE;
SET i = 1;
SET j = j + 1;
END WHILE;
END$$
DELIMITER ;
调用存储过程函数如下:
CALL 失败位置(8,6);
执行存储过程后函数如下: