result  表长什么样子

就是成绩cj，只是改了个名字哈。

我再看一下，如何替换 update 哈，谢谢！

kh有274个存在相同的记录，什么意思？

统计十四万条记录的排名对 vfp 来说应该是小菜一碟，耗费二百多分钟绝对不可接受
你这需求不明确，没定义成绩相同时如何排名，ex: 三个人的成绩分别是 100,100,99,98,98，排名是 1,1,2,3,3 还是 1,1,3,4,4 ？

CLOSE DATABASES

LOCAL xfile,xtmpfile,xtime
xtime = DATETIME()
USE cj IN 0
SELECT cj
REPLACE ALL ywlkx WITH 0,ywbsx WITH 0,ywbxx WITH 0,sxlkx WITH 0,sxbsx WITH 0,sxbxx WITH 0,yylkx WITH 0,yybsx WITH 0,yybxx WITH 0

xfile = cjsort('cj','yw')
UPDATE a SET a.ywlkx=b.rec from cj a,&xfile b WHERE a.yw=b.yw
USE IN &xfile

xfile = cjsort('cj','sx')
UPDATE a SET a.sxlkx=b.rec from cj a,&xfile b WHERE a.sx=b.sx
USE IN &xfile

xfile = cjsort('cj','yy')
UPDATE a SET a.yylkx=b.rec from cj a,&xfile b WHERE a.yy=b.yy
USE IN &xfile

* 本市序
SELECT cj
INDEX on dsdm TO cj

xtmpfile = SYS(2015)
SELECT distinct dsdm FROM cj INTO CURSOR &xtmpfile READWRITE 
SELECT &xtmpfile
SCAN 
    SELECT dsdm,yw,sx,yy FROM cj WHERE dsdm=&xtmpfile..dsdm INTO CURSOR &xtmpfile.1 READWRITE 
    xfile = cjsort('&xtmpfile.1','yw')
    UPDATE a SET a.ywbsx=b.rec FROM cj a,&xfile b WHERE a.dsdm=&xtmpfile..dsdm AND a.yw=b.yw
    USE IN &xfile

    xfile = cjsort('&xtmpfile.1','sx')
    UPDATE a SET a.sxbsx=b.rec FROM cj a,&xfile b WHERE a.dsdm=&xtmpfile..dsdm AND a.sx=b.sx
    USE IN &xfile
    
    xfile = cjsort('&xtmpfile.1','yy')
    UPDATE a SET a.yybsx=b.rec FROM cj a,&xfile b WHERE a.dsdm=&xtmpfile..dsdm AND a.yy=b.yy
    USE IN &xfile

    USE IN &xtmpfile.1
ENDSCAN
USE IN &xtmpfile

* 本县序
SELECT cj
INDEX on xqdm TO cj

xtmpfile = SYS(2015)
SELECT distinct xqdm FROM cj INTO CURSOR &xtmpfile READWRITE 
SELECT &xtmpfile
SCAN 
    SELECT xqdm,yw,sx,yy FROM cj WHERE xqdm=&xtmpfile..xqdm INTO CURSOR &xtmpfile.1 READWRITE 

    xfile = cjsort('&xtmpfile.1','yw')
    UPDATE a SET a.ywbxx=b.rec FROM cj a,&xfile b WHERE a.xqdm=&xtmpfile..xqdm AND a.yw=b.yw
    USE IN &xfile
    
    xfile = cjsort('&xtmpfile.1','sx')
    UPDATE a SET a.sxbxx=b.rec FROM cj a,&xfile b WHERE a.xqdm=&xtmpfile..xqdm AND a.sx=b.sx
    USE IN &xfile

    xfile = cjsort('&xtmpfile.1','yy')
    UPDATE a SET a.yybxx=b.rec FROM cj a,&xfile b WHERE a.xqdm=&xtmpfile..xqdm AND a.yy=b.yy
    USE IN &xfile

    USE IN &xtmpfile.1
ENDSCAN
USE IN &xtmpfile

MESSAGEBOX( DATETIME()-xtime)

SELECT * FROM cj 

RETURN

FUNCTION cjsort
LPARAMETERS Pfile,Psortfld
* 返回一个排序的临时文件

LOCAL xfile,xcj,xii
xfile = SYS(2015)
SELECT &psortfld,CAST(0 as int) rec FROM &Pfile ORDER BY &psortfld DESC INTO CURSOR &xfile READWRITE 
SELECT &xfile
REPLACE ALL rec WITH RECNO()

xcj = 999999
SCAN 
    IF &psortfld=xcj
    ELSE 
        xii = rec
        xcj = &psortfld
    ENDIF 
    REPLACE rec WITH xii        
ENDSCAN

RETURN xfile

仅对三个单科进行了排位，用时6秒
其他需要排序的字段可参照修改程序

采用后者，谢谢！
耗时  220  秒   哈

[此贴子已经被作者于2023-8-13 16:34编辑过]

我得认真研究这个，谢谢！

这个可暂不考虑，只管分 联考、本市、本县 三个区域 按成绩排序即可，谢谢！

Close Databases
Use cj In 0
Select xh, dsdm, xqdm, xxdm, kh ;
    ,yw, Cast(0 as I) ywlkx, Cast(0 as I) ywbsx, Cast(0 as I) ywbxx ;
    ,sx, Cast(0 as I) sxlkx, Cast(0 as I) sxbsx, Cast(0 as I) sxbxx ;
    ,yy, Cast(0 as I) yylkx, Cast(0 as I) yybsx, Cast(0 as I) yybxx ;
    from cj into cursor result Readwrite

m.ss = Seconds()
For m.ii = 1 to ALines(aTemp, 'yw,sx,yy', ',')
    m.km = aTemp[ii]

Wait window nowait noclear '统计 ' + m.km + ' 联考排名 ...'
    Select &km km, Count(*) cnt, Cast(0 as I) pm ;
        from result group by km order by km desc into cursor temp Readwrite
    Update temp set pm = Recno()
    Select a.km, 1 + Nvl((select Sum(cnt) cnt from temp b where b.pm < a.pm), 0) pm from temp a into cursor temp2
    Update t set t.&km.lkx = s.pm from result t inner join temp2 s on t.&km = s.km

Wait window nowait noclear '统计 ' + m.km + ' 市排名 ...'
    Select &km km, dsdm, Count(*) cnt, Cast(0 as I) pm ;
        from result group by dsdm, km order by km desc into cursor temp Readwrite
    Update temp set pm = Recno()
    Select a.km, a.dsdm, 1 + Nvl((select Sum(cnt) cnt from temp b where (a.dsdm = b.dsdm) and (b.pm < a.pm)), 0) pm from temp a into cursor temp2
    Update t set t.&km.bsx = s.pm from result t inner join temp2 s on t.&km = s.km and t.dsdm = s.dsdm

Wait window nowait noclear '统计 ' + m.km + ' 乡排名 ...'
    Select &km km, xqdm, Count(*) cnt, Cast(0 as I) pm ;
        from result group by xqdm, km order by km desc into cursor temp Readwrite
    Update temp set pm = Recno()
    Select a.km, a.xqdm, 1 + Nvl((select Sum(cnt) cnt from temp b where (a.xqdm = b.xqdm) and (b.pm < a.pm)), 0) pm from temp a into cursor temp2
    Update t set t.&km.bxx = s.pm from result t inner join temp2 s on t.&km = s.km and t.xqdm = s.xqdm
EndFor
Use in temp2
Use in temp

Wait window nowait noclear '耗时 ' + Transform(Seconds() - m.ss)
Select result
Locate
Browse

[此贴子已经被作者于2023-8-13 18:16编辑过]