mysql - rownum 구현

set @web_load_avr = (select avg(load_avr) from tbl_server_check where svc_type='web');
set @web_conn_avr = (select avg(connect_cnt) from tbl_server_check where svc_type='web');
set @web_load_conn_ratio = @web_conn_avr / @web_load_avr;

set @db_load_avr = (select avg(load_avr) from tbl_server_check where svc_type='db');
set @db_conn_avr = (select avg(connect_cnt) from tbl_server_check where svc_type='db');
set @db_load_conn_ratio = @db_conn_avr / @db_load_avr;

set @rownum_1=0;
set @rownum_2=0;

select web.rownum, web.host_name web_name, db.host_name db_name, web.host_ip web_ip, db.host_ip db_ip, web.load_total web_load_total, db.load_total db_load_total
from
(
select (@rownum_1:=@rownum_1+1) rownum, host_name, host_ip, (load_avr*@web_load_conn_ratio + connect_cnt) load_total
from tbl_server_check
where svc_type='web'
order by load_total desc
) web,
(
select (@rownum_2:=@rownum_2+1) rownum, host_name, host_ip, (load_avr*@db_load_conn_ratio + connect_cnt) load_total
from tbl_server_check
where svc_type='db'
order by load_total desc
) db
where web.rownum = db.rownum

 

======================================================================================================================

select seq_no, @RNUM:=@RNUM+1 AS ROWNUM
from tbl_encode_file  file, (SELECT @RNUM:=0) R
where file.file_size > 2*1024*1024*1024