请帮忙，学科成绩尽可能快的排序，谢谢！ - 编程论坛

#2

sdta2023-08-12 22:08

空字段太多了，影响排序的速度。
那些内容排序，能说清楚点吗？字段顺序？

#3

xinjie2023-08-13 06:09

贵宾就这素质？让人给你写程序啊？

#4

schtg2023-08-13 06:13

回复 2楼 sdta

谢谢！新传数据表中的 yw 字段以后的全部字段，均要排出3个名次(同分同名占位)：所有参考考生拉通的名次(14w中的)、以地市代码dsdm为准的地市名次、以县区代码xqdm为准的县区名次，可分别用lkx、bsx、bxx来表示这三个字段。谢谢！
我自己统计，需要220秒，希望大侠支招，可以更快一点，谢谢！

[此贴子已经被作者于2023-8-14 19:19编辑过]

#5

schtg2023-08-13 06:16

回复 3楼 xinjie

自学，水平不高，只是混得时间长一点哈，见谅

#6

sdta2023-08-13 07:28

以下是引用schtg在2023-8-13 06:13:33的发言：

谢谢！新传数据表中的 yw 字段以后的全部字段，均要排出3个名次(同分同名占位)：所有参考考生拉通的名次(14w中的)、以地市代码dsdm为准的地市名次、以县区代码xqdm为准的县区名次，可分别用lkx、bsx、bxx来表示这三个字段。谢谢！
数据表：https://down.bccn.net/12536.html
我自己统计，需要220秒，希望大侠支招，可以更快一点，谢谢！

还是没看明白，给个最后排序的字段列表看看

#7

吹水佬2023-08-13 07:56

回复 4楼 schtg

看不明你说的，你写的代码呢？

#8

schtg2023-08-13 08:35

回复 6楼 sdta

只有本站会员才能查看附件，请登录

#9

schtg2023-08-13 08:42

回复 7楼吹水佬

仿照 sdta 先生的代码改写的哈，谢谢吹版主！谢谢sdta先生！

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

#10

sdta2023-08-13 09:47

速度慢的原因可能是来自于　update 命令吧

#11

sdta2023-08-13 10:05

result 表长什么样子

#12

schtg2023-08-13 10:52

回复 11楼 sdta

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

#13

schtg2023-08-13 10:53

回复 10楼 sdta

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

#14

吹水佬2023-08-13 11:31

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

#15

csyx2023-08-13 12:02

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

#16

laowan0012023-08-13 12:31

程序代码：

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秒
其他需要排序的字段可参照修改程序

#17

schtg2023-08-13 16:29

回复 15楼 csyx

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

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

#18

schtg2023-08-13 16:30

回复 16楼 laowan001

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

#19

schtg2023-08-13 16:33

回复 14楼吹水佬

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

#20

csyx2023-08-13 18:05

程序代码：

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编辑过]

#21

schtg2023-08-13 19:02

回复 20楼 csyx

我也得研究一下，谢谢！

#22

csyx2023-08-13 20:18

前面代码用 SQL 计算排名还是不够快，应该是 sql 没学好，只好改成 vfp 语法，学艺不精啊。
虽然代码多了些，但耗时减少到两秒左右，以空间换时间还是值得的

程序代码：

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.科目 = aTemp[ii]

? '统计 ' + m.科目 + ' 总排名 ... '
    Select &科目成绩, 0 分组, Count(*) 人数, Cast(0 as I) 排名 ;
        from result group by 成绩 order by 成绩 desc into cursor temp Readwrite
    SetRank('temp')
    Update t set t.&科目.lkx = s.排名 from result t inner join temp s on t.&科目 = s.成绩

? '统计 ' + m.科目 + ' 市排名 ... '
    Select &科目成绩, dsdm 分组, Count(*) 人数, Cast(0 as I) 排名 ;
        from result group by 分组, 成绩 order by 分组, 成绩 desc into cursor temp Readwrite
    SetRank('temp')
    Update t set t.&科目.bsx = s.排名 from result t inner join temp s on t.&科目 = s.成绩 and t.dsdm = s.分组

? '统计 ' + m.科目 + ' 乡排名 ... '
    Select &科目成绩, xqdm 分组, Count(*) 人数, Cast(0 as I) 排名 ;
        from result group by 分组, 成绩 order by 分组, 成绩 desc into cursor temp Readwrite
    SetRank('temp')
    Update t set t.&科目.bxx = s.排名 from result t inner join temp s on t.&科目 = s.成绩 and t.xqdm = s.分组
EndFor
Use in temp

? '总耗时', Seconds() - m.ss
Select result
Locate
Browse

Function SetRank(als)
    Local n分组, n排名
    Select (m.als)
    Locate
    m.n分组 = -1
    Scan all
        If 分组 != m.n分组
            m.n分组 = 分组
            m.n排名 = 1
        EndIf
        Replace next 1 排名 with m.n排名
        m.n排名 = m.n排名 + 人数
    EndScan
EndFunc

把我定义/使用的字段/变量，拼音改成了汉字，这样容易看明白

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

#23

schtg2023-08-14 05:35

回复 22楼 csyx

好好，谢谢！
我正在研究、测试，非常感谢！

[此贴子已经被作者于2023-8-14 11:57编辑过]

#24

laowan0012023-08-14 07:55

程序代码：

CLOSE DATABASES

LOCAL xfile,xtmpfile,xtime
xtime = SECONDS()
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( SECONDS()-xtime)

SELECT * FROM cj

RETURN

FUNCTION cjsort
LPARAMETERS Pfile,Psortfld
* 返回一个排序的临时文件
LOCAL xfile,xii
xfile = SYS(2015)
SELECT &psortfld,COUNT(*) cnt,CAST(0 AS int) rec FROM &Pfile GROUP BY &psortfld ORDER BY &psortfld DESC INTO CURSOR &xfile READWRITE
SELECT &xfile
xii = 1
SCAN
    REPLACE rec WITH xii
    xii = xii + cnt
ENDSCAN
RETURN xfile

22楼计算名次的方法效率很高

修改了16楼的代码后，用时2.5秒

#25

schtg2023-08-14 11:28

回复 24楼 laowan001

好好，谢谢！
我在消化、测试各位大侠的算法。
非常感谢各位的无私赐教！！！

#26

吹水佬2023-08-16 10:41

不用SQL也不慢

程序代码：

USE cj
DIMENSION afs[FCOUNT()-5]    && yw及之后的字段名列表
FOR i=6 TO FCOUNT()
    afs[i-5] = FIELD(i)
ENDFOR
USE
cmd = "CREATE CURSOR mc (xh n(9),dsdm n(6),xqdm n(8),xxdm n(10),kh v(16)"
FOR i=1 TO ALEN(afs)
    cmd = cmd + "," + afs[i]+" n(7,1)," + afs[i]+"lkx I," + afs[i]+"bsx I," + afs[i]+"bxx I"
ENDFOR
cmd = cmd + ")"
EXECSCRIPT(cmd)
APPEND FROM cj
tm=SECONDS()
FOR i=1 TO 3 &&ALEN(afs)
    tj_mc(afs[i])
ENDFOR
? SECONDS()-tm
SELECT * FROM mc
CLOSE TABLES ALL
RETURN

FUNCTION tj_mc(kmm)
    INDEX on &kmm TAG kmm DESCENDING
    tj_lkx(kmm)    &&联考名次
    INDEX on dsdm*1000+&kmm TAG kmm DESCENDING
    tj_dsdm(kmm)   &&本市名次
    INDEX on xqdm*1000+&kmm TAG kmm DESCENDING
    tj_xqdm(kmm)   &&本县名次
ENDFUNC

FUNCTION tj_lkx(kmm)
    m  = 1
    n  = 1
    fs = -1
    SCAN
        IF EVALUATE(kmm) != fs
            fs = EVALUATE(kmm)
            m  = n
        ENDIF
        n = n + 1
        REPLACE (kmm+"lkx") WITH m
    ENDSCAN
ENDFUNC

FUNCTION tj_dsdm(kmm)
    m  = 1
    n  = 1
    fs = -1
    dm = 0
    SCAN
        IF dsdm != dm
            dm = dsdm
            m = 1
            n = 1
        ENDIF
        IF EVALUATE(kmm) != fs
            fs = EVALUATE(kmm)
            m  = n
        ENDIF
        n = n + 1
        REPLACE (kmm+"bsx") WITH m
    ENDSCAN
ENDFUNC

FUNCTION tj_xqdm(kmm)
    m  = 1
    n  = 1
    fs = -1
    dm = 0
    SCAN
        IF xqdm != dm
            dm = xqdm
            m = 1
            n = 1
        ENDIF
        IF EVALUATE(kmm) != fs
            fs = EVALUATE(kmm)
            m  = n
        ENDIF
        n = n + 1
        REPLACE (kmm+"bxx") WITH m
    ENDSCAN
ENDFUNC

#27

schtg2023-08-16 11:37

回复 26楼吹水佬

谢谢！

#28

sdta2024-04-07 10:03

回头看，将N科科目分割成N个表，再进行数据处理，速度会提高不少的