查询每个地区最大年龄学生的所有信息
select * from student where age in (select max(age) from student group by home);
因为按地区获取最大值，后来你只安年龄查 没有过滤地区 所以导致错误 比如 天山派 最大年龄 85 武当派100 你获取是age in (85,100) ，如果武当也有85，也有100 武当就会出现2条
-- 创建存储过程CREATE PROCEDURE shxc42()BEGIN-- 定义变量DECLARE s int DEFAULT 0;DECLARE nl int DEFAULT 20;DECLARE jia varchar(255);-- 定义游标，并将sql结果集赋值到游标中DECLARE report CURSOR FOR select max(age),home from student group by home;-- 声明当游标遍历完后将标志变量置成某个值DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;-- 打开游标open report;-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致fetch report into nl,jia;-- 当s不等于1，也就是未遍历完时，会一直循环while s<>1 dounion select * from student where age=nl and home=jia;-- 将游标中的值再赋值给变量，供下次循环使用fetch report into nl,jia;-- 当s等于1时表明遍历以完成，退出循环end while;-- 关闭游标close report;END;
表结构和数据如下：
DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`id` int(255) NOT NULL,`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`age` int(255) DEFAULT NULL,`sex` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`home` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`tel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO `student` VALUES ('1', '张无忌', '18', '男', '武当山', '15058584792');INSERT INTO `student` VALUES ('2', '张无忌', '19', '男', '明教', '15058584666');INSERT INTO `student` VALUES ('3', '张三丰', '150', '男', '武当山', '15258584797');INSERT INTO `student` VALUES ('4', '宋元桥', '99', '男', '武当山', '15058566792');INSERT INTO `student` VALUES ('5', '赵敏', '22', '女', '大元', '17658584797');INSERT INTO `student` VALUES ('6', '周芷若', '24', '女', '峨眉派', '17658584793');INSERT INTO `student` VALUES ('7', '灭绝师太', '90', '女', '峨眉派', '17658584798');INSERT INTO `student` VALUES ('8', '小龙女', '99', '女', '古墓派', '15258668479');INSERT INTO `student` VALUES ('9', '杨过', '100', '男', '古墓派', '18558584796');INSERT INTO `student` VALUES ('10', '郭靖', '35', '男', '桃花岛', '15258584792');INSERT INTO `student` VALUES ('11', '黄蓉', '32', '女', '桃花岛', '17658584798');INSERT INTO `student` VALUES ('12', '乔峰', '30', '男', '大辽', '17658584790');INSERT INTO `student` VALUES ('13', '段誉', '25', '男', '大理', '17658566692');INSERT INTO `student` VALUES ('14', '虚竹', '28', '男', '灵鹫宫', '15258587792');INSERT INTO `student` VALUES ('15', '王语嫣', '20', '女', '曼陀山庄', '15258589792');INSERT INTO `student` VALUES ('16', '老顽童', '200', '男', '全真教', '1525858792');INSERT INTO `student` VALUES ('17', '鸠摩智', '60', '男', '吐蕃', '18258584792');INSERT INTO `student` VALUES ('18', '无崖子', '95', '男', '聋哑谷', '18258584792');INSERT INTO `student` VALUES ('19', '李秋水', '98', '女', '西域', '152585999552');INSERT INTO `student` VALUES ('20', '天山童姥', '99', '女', '灵鹫宫', '15266584799');INSERT INTO `student` VALUES ('21', '段正明', '66', '男', '大理', '15258578398');INSERT INTO `student` VALUES ('22', '金轮法王', '68', '男', '吐蕃', '18558590792');INSERT INTO `student` VALUES ('23', '扫地僧', '300', '男', '少林寺', '18558599792');INSERT INTO `student` VALUES ('24', '萧远山', '70', '男', '大辽', '13658584792');INSERT INTO `student` VALUES ('25', '慕容博', '100', '男', '燕子坞', '13658584792');INSERT INTO `student` VALUES ('26', '阿朱', '26', '女', '燕子坞', '13658585692');INSERT INTO `student` VALUES ('27', '许尘', '46', '男', '灵武大陆', '18595831579');INSERT INTO `student` VALUES ('28', '迟长夜', '32', '男', '灵武大陆', '18595831314');INSERT INTO `student` VALUES ('29', '古遥', '18', '男', '灵武大陆', '18595835210');INSERT INTO `student` VALUES ('30', '李逍遥', '1000', '男', '蜀山', '15266546965');
如果觉得《mysql存储过程之循环遍历查询结果集》对你有帮助，请点赞、收藏，并留下你的观点哦！
