-- ----------------------------

-- 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 ('', '张三', '', '北京', '沈阳');

INSERT INTO `temp` VALUES ('', '张三', '', '沈阳', '哈尔滨');

INSERT INTO `temp` VALUES ('', '李四', '', '保定', '天津');

INSERT INTO `temp` VALUES ('', '李四', '', '天津', '东莞');

INSERT INTO `temp` VALUES ('', '李四', '', '东莞', '广州');

INSERT INTO `temp` VALUES ('', '王五', '', '保定', '石家庄');

INSERT INTO `temp` VALUES ('', '王五', '', '石家庄', '宁晋');

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

-- ----------------------------

-- 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 ('', '张三', '', '北京', '哈尔滨');

INSERT INTO `temp1` VALUES ('', '李四', '', '保定', '广州');

INSERT INTO `temp1` VALUES ('', '王五', '', '保定', '宁晋');