DROP PROCEDURE

 IF EXISTS `P_GetMonitorPeople`;

 CREATE PROCEDURE P_GetMonitorPeople (IN fgid INT, IN mins INT,in lens INT)

 BEGIN

 IF fgid>0&&mins>0&&lens>0 THEN

 BEGIN

 -- 建表tb_temp1

 DROP TABLE IF EXISTS tb_temp1;

 CREATE TEMPORARY TABLE tb_temp1(

 `Tmp_Id` int UNSIGNED NOT NULL AUTO_INCREMENT,

 `GatherID` int NOT NULL,

 `PhoneMac` varchar(12) NOT NULL,

 `number` int NOT NULL,

 PRIMARY KEY (`Tmp_Id`)

 ) ENGINE = MYISAM DEFAULT charset = utf8;

 -- 填充tb_temp1

 INSERT into tb_temp1(GatherID,PhoneMac,number)

 select gatherid, phonemac,count(phonemac) as number from (

 select DISTINCT s.GatherID,s.phonemac from tb_app_gather_mac_shot s

 LEFT JOIN tb_web_config_gather g on g.gatherid=s.gatherid

 where s.logtime BETWEEN DATE_SUB(NOW(),INTERVAL mins MINUTE) and NOW() and g.floorid=(select floorid from tb_web_floors where groupid=fgid)

 ORDER BY s.logtime) as tb GROUP BY phonemac order by number DESC LIMIT lens;

 select DISTINCT g.gatherid, t.phonemac,g.locationx,g.locationy from tb_temp1 t

 LEFT JOIN tb_web_config_gather g on g.gatherid=t.gatherid;

 END;

 END IF;

 #销毁内存表

 DROP TABLE IF EXISTS tb_temp1;

 END

 -- 显示

 CALL P_GetMonitorPeople(3,1,50);

DROP PROCEDURE

IF EXISTS P_GetMonitorData;

CREATE PROCEDURE P_GetMonitorData(fgid int,mins int,lens int,mac varchar(12))

BEGIN

DECLARE tmpid int ;

DECLARE gid varchar(10) ;

DECLARE done INT DEFAULT FALSE;

DECLARE pregatherid int DEFAULT 0;

DECLARE str VARCHAR(200);

-- 定义游标

DECLARE rs CURSOR for select Tmp_Id,GatherID from tb_temp2;

-- 错误定义，标记循环结束

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

-- 打开游标

OPEN rs;

-- 循环执行

REPEAT

FETCH rs into tmpid,gid ;

IF NOT done THEN

#执行操作

IF pregatherid=gid && pregatherid <> 0 THEN

DELETE from tb_temp2 where Tmp_Id=tmpid;

ELSE

SET pregatherid=gid;

-- SET str=CONCAT(gid,str);

END IF;

END IF;

#当_done=FALSE时退出被循

UNTIL done

END REPEAT;

/*关闭游标*/

CLOSE rs;

select * from tb_temp2;

SET done=FALSE;#只有定义为false，新的循环才能继续。

END