急：请问如何实现表数据的排名
本帖最后由 willxue 于 2013-04-01 19:24 编辑表t1如下：
name nums
a 3
b 4
c 7
d 4
希望保持name字段顺序不变，增加排名字段（rank_nums）并输出结果如下：
name nums rank_nums
a 3 4
b 4 2
c 7 1
d 4 2
请问，用什么语句能实现，若有必要，用临时表也行，多谢啦！
本帖最后由 george002 于 2013-04-04 21:38 编辑
create table t2(name char(1),num int,rank int);
create procedure t1_proc()
define v_recc int;
define v_rank int;
define v_name char(1);
define v_num int;
define v_num1 int;
let v_recc = 0;
let v_rank = 0;
let v_num1 = 0;
foreach cur_upd_tb_tmp for
select name,num
into v_name, v_num
from t1
order by num desc
if ( v_num1 != v_num ) then
let v_num1=v_num;
let v_recc = v_recc + 1;
--let v_rank = v_rank + 1;
let v_rank = v_recc;
insert into t2 values(v_name,v_num,v_recc);
else
insert into t2 values(v_name,v_num,v_rank);
let v_recc = v_recc + 1;
end if
end foreach;
end procedure;
execute procedure t1_proc();
select * from t2 order by name; 楼上真是太伟大了，这么复杂的程序都有，收藏啦，非常感谢！
页:
[1]