成功实现sqlserver的row_number函数方法

发布时间：2023-08-18 15:25:13 所属栏目：MySql教程来源：

导读：本文主要给大家介绍实现sqlserver的row_number函数方法，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，亿速云累计多年的实践经验可分享给大家。

1. 使用临时表

CR

本文主要给大家介绍实现sqlserver的row_number函数方法，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，亿速云累计多年的实践经验可分享给大家。

1. 使用临时表

CREATE DEFINER=`root`@`%` PROCEDURE `sp_getMonitorInfo`(IN d_itemId INT, IN d_configId INT, d_count_num INT )

begin

实现sqlserver的row_number函数方法

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) 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. 使用临时变量

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 :=@i + 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;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!