清华大佬耗费三个月吐血整理的几百G的资源,免费分享!....>>>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | delimiter $$ drop procedure if exists findErrorLifeRoute $$ CREATE PROCEDURE findErrorLifeRoute() BEGIN declare routeId,routeStopId BIGINT ; declare str,tmp_street VARCHAR (255) ; DECLARE l_done,num,life,localId,tmp_localId INT DEFAULT 0; -- 声明两个游标,第二个游标使用到第一个的查询结果 DECLARE grade_csr cursor FOR SELECT route_id FROM routes where LIFE_CYCLE_STATUS<39998 and LIFE_CYCLE_STATUS>35001; DECLARE class_csr cursor FOR SELECT LOCATION_INNER_ID,STREET FROM route_stops WHERE ROUTE_ID=routeId order by STOP_NUMBER asc ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1; OPEN grade_csr; -- 第一个循环 grade_loop: LOOP -- Loop through org_grade FETCH grade_csr into routeId; IF l_done=1 THEN LEAVE grade_loop; END IF; SET num=0; set tmp_street= null ; set tmp_localId=0; OPEN class_csr; -- 第二个循环 class_loop: LOOP -- Loop through class in grade. FETCH class_csr INTO localId,str; IF l_done=1 THEN LEAVE class_loop; END IF; if localId =tmp_localId or (str is not null and str=tmp_street) THEN set tmp_street=str; set tmp_localId=localId; else set num=num+1; set tmp_street=str; set tmp_localId=localId; end if; -- 结束第一个循环 END LOOP class_loop; CLOSE class_csr; SET l_done=0; set life=36000+(num-1)*100+6; INSERT into route_tmp ( select ROUTE_ID,LIFE_CYCLE_STATUS,num,life,ACCOUNT_ID,str from routes r where r.ROUTE_ID=routeId and LIFE_CYCLE_STATUS=life ); -- 结束第二个循环 END LOOP grade_loop; CLOSE grade_csr; END $$ delimiter ; |