实现sqlserver的row_number函数方法
本文主要给大家介绍实现sqlserver的row_number函数方法,其所涉及的东西,从理论知识来获悉,有很多书籍、文献可供大家参考,从现实意义角度出发,创新互联累计多年的实践经验可分享给大家。
创新互联-专业网站定制、快速模板网站建设、高性价比夏县网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式夏县网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖夏县地区。费用合理售后完善,十余年实体公司更值得信赖。
1. 使用临时表
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) 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;
看了以上介绍实现sqlserver的row_number函数方法,希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限,难免会有不足和需要补充的地方,大家可以继续关注创新互联行业资讯板块,会定期给大家更新行业新闻和知识,如有需要更加专业的解答,可在官网联系我们的24小时售前售后,随时帮您解答问题的。
本文标题:实现sqlserver的row_number函数方法
转载来于:http://myzitong.com/article/ppppso.html