MySQL interval fetch data, implement SQL Server's Row_number () function

1. Using temporary tables

CREATE definer= ' root ' @ '% ' PROCEDURE ' sp_getmonitorinfo ' (in D_itemid int, in D_configid int, d_count_num int)

Begin

Set @count = 0;

Set @num = 0;

SELECT @count: =count (1) from better. Monitorinfo where Itemid=d_itemid and Configid=d_configid;

IF @count <300

Then

SELECT Id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend,time,configid,itemid from Monitorinfo where Itemid=itemid and Configid=configid;

ELSE

SET @num = round (@count/d_count_num,0);

Select @num;

Create temporary table Tmp_monitorinfo

(

tmp_id Int (4) primary key NOT NULL auto_increment,

ID Int (4) is not NULL,

CPU int,

CPU1 int,

CPU2 int,

CPU3 int,

Diskread int,

Diskwrite int,

Memory int,

networkreceive int,

Networksend int,

Time varchar (40),

Configid int,

itemId int

);

Insert into Tmp_monitorinfo (Id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend,time, CONFIGID,ITEMID)

Select Id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend,time,configid,itemid from Monitorinfo

where Itemid=d_itemid and Configid=d_configid;

Select Id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend,time,configid,itemid from Tmp_ Monitorinfo

where tmp_id% @num = 0;

drop table Tmp_monitorinfo;

END IF;

End

2. Using Temporary variables

CREATE definer= ' root ' @ '% ' PROCEDURE ' sp_getmonitorinfo_2 ' (in D_itemid int, in D_configid int, d_count_num int)

Begin

Set @count = 0;

Set @num = 0;

SELECT @count: =count (1) from better. Monitorinfo where Itemid=d_itemid and Configid=d_configid;

IF @count <300

Then

SELECT Id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend,time,configid,itemid from Monitorinfo where Itemid=itemid and Configid=configid;

ELSE

SET @num = round (@count/d_count_num,0);

Select @num;

Set @i = 0;

SELECT * FROM (

Select @i: [email protected] + 1 as Tmp_id,id,cpu,cpu1,cpu2,cpu3,diskread,diskwrite,memory,networkreceive,networksend , Time,configid,itemid from Monitorinfo

where Itemid=d_itemid and Configid=d_configid) AA

where aa.tmp_id% @num = 0;

END IF;

End

This article is from the "Love Work Love Life" blog, please be sure to keep this source http://4453154.blog.51cto.com/4443154/1920350

MySQL interval fetch data, implement SQL Server's Row_number () function