基本表temp
包括 name, type, sendCity, getCity
分别对应物流送货司机名, 倒车的第几段, 发货城市, 收货城市
表结构
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`id` int(16) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`type` varchar(32) DEFAULT NULL,
`sendCity` varchar(32) DEFAULT NULL,
`getCity` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('7', '张三', '2', '北京', '沈阳');
INSERT INTO `temp` VALUES ('8', '张三', '3', '沈阳', '哈尔滨');
INSERT INTO `temp` VALUES ('9', '李四', '1', '保定', '天津');
INSERT INTO `temp` VALUES ('10', '李四', '2', '天津', '东莞');
INSERT INTO `temp` VALUES ('11', '李四', '3', '东莞', '广州');
INSERT INTO `temp` VALUES ('12', '王五', '1', '保定', '石家庄');
INSERT INTO `temp` VALUES ('13', '王五', '2', '石家庄', '宁晋');
这里是分段的,需要将姓名是“张三” 的第二,第三段合成一个 记录,也就是 “张三” “北京” “哈尔滨”
物流司机可能是 1,2段,可能是1,2,3段,可能是2,3段所以在这里写一个存储过程,将合并后的结果放到另一个表中,
存储过程用到双游标嵌套,仅做记录
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE _name varchar(64);
declare _maxType varchar(32);
declare _minType varchar(32);
declare _type varchar(64);
declare _sendCity varchar(64);
declare _getCity varchar(64);
#declare _tempName varchar(64);
declare _tempSend varchar(64);
declare _tempGet varchar(64);
DECLARE cursorOuter CURSOR for select name, max(type) maxType, min(type) minType from temp group by name;
DECLARE cursorInner CURSOR for select type, sendCity, getCity from temp where name=_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; #循环终止的标志,游标中如果没有数据就设置done为1
#select now();
OPEN cursorOuter;
FETCH cursorOuter INTO _name, _maxType, _minType;
WHILE (done=0) DO
begin
open cursorInner;
fetch cursorInner into _type, _sendCity, _getCity;
while (done=0) DO
if _type = _maxType then
set _tempGet = _getCity;
elseif _type = _minType then
set _tempSend = _sendCity;
end if;
FETCH cursorInner into _type, _sendCity, _getCity;
end while;
close cursorInner;
end;
INSERT into temp1(name, type, sendCity, getCity) values(_name, _type, _tempSend, _tempGet);
SET done = 0;
## 赋值下一个游标
FETCH cursorOuter INTO _name, _maxType, _minType;
END WHILE;
## 关闭
CLOSE cursorOuter;
END
最后的 temp1 表结构和数据是这样的:
-- ----------------------------
-- Table structure for `temp1`
-- ----------------------------
DROP TABLE IF EXISTS `temp1`;
CREATE TABLE `temp1` (
`id` int(16) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`type` varchar(32) DEFAULT NULL,
`sendCity` varchar(32) DEFAULT NULL,
`getCity` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of temp1
-- ----------------------------
INSERT INTO `temp1` VALUES ('19', '张三', '3', '北京', '哈尔滨');
INSERT INTO `temp1` VALUES ('20', '李四', '3', '保定', '广州');
INSERT INTO `temp1` VALUES ('21', '王五', '2', '保定', '宁晋');
遇到的问题:
1、存储过成总是提示 nodata,查询后 添加
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; #循环终止的标志,游标中如果没有数据就设置done为1
SET done = 0;
while循环以WHILE (done=0) DO 作为判断。
2、游标嵌套后需要加
begin……end;
内层循环是包含在其中的;