试试这段sql在你的电脑上花多少时间，我这本本上大概也1.5秒，服务器上就秒出结果
三段排名算法一模一样，唯一不同的是 group by 多一个字段，更新回源表时 join on 连接条件也同样多一个字段
程序代码：
Close Databases
Clear
Use xqpx Exclusive
ss = Seconds()
*-- 联考排名
Select xk, jf, Cast(0 as I) rank ;
from xqpx group by xk,jf order by xk,jf desc into cursor temp1 readwrite
Update temp1 set rank = Recno()
Select xk, Min(rank) as rank from temp1 group by xk into cursor temp2
Update t set t.rank = 1 + t.rank - s.rank from temp1 t inner join temp2 s on t.xk == s.xk
Update t set t.lkpx = s.rank from xqpx t inner join temp1 s on t.jf == s.jf
*-- 地市排名
Select dsmc, xk, jf, Cast(0 as I) rank ;
from xqpx group by dsmc,xk,jf order by dsmc,xk,jf desc into cursor temp1 readwrite
Update temp1 set rank = Recno()
Select dsmc, xk, Min(rank) as rank from temp1 group by dsmc,xk into cursor temp2
Update t set t.rank = 1 + t.rank - s.rank ;
from temp1 t inner join temp2 s on t.xk == s.xk and t.dsmc == s.dsmc
Update t set t.bspx = s.rank ;
from xqpx t inner join temp1 s on t.jf == s.jf and t.xk == s.xk and t.dsmc == s.dsmc
*-- 县区排名
Select dsmc, xqmc, xk, jf, Cast(0 as I) rank ;
from xqpx group by dsmc,xqmc,xk,jf order by dsmc,xqmc,xk,jf desc into cursor temp1 readwrite
Update temp1 set rank = Recno()
Select dsmc, xqmc, xk, Min(rank) as rank from temp1 group by dsmc,xqmc,xk into cursor temp2
Update t set t.rank = 1 + t.rank - s.rank from temp1 t ;
inner join temp2 s on t.xk == s.xk and t.dsmc == s.dsmc and t.xqmc == s.xqmc
Update t set t.bxpx = s.rank ;
from xqpx t inner join temp1 s ;
on t.jf == s.jf and t.xk == s.xk and t.dsmc == s.dsmc and t.xqmc == s.xqmc
Use in temp2
Use in temp1
Wait window nowait noclear '耗时:' + Transform(Seconds()-ss)
Select xqpx
Locate
Browse
[此贴子已经被作者于2023-3-21 08:09编辑过]
