请教如何统计成绩排名和同分人数 - 编程论坛

#2

sdta2023-02-28 18:05

程序代码：

* VFP9 代码
CLOSE DATABASES
SELECT * FROM DBF\aa ORDER BY bkzydm, zf DESC INTO CURSOR temp READWRITE
BLANK FIELDS pm, bl ALL
lczydm = SPACE(0)
lnzf = 0
lnzrs = 0
SCAN
    lnzrs = lnzrs + 1
    IF bkzydm == lczydm
        IF zf != lnzf
            lnmc = lnzrs
            lntf = 1
        ELSE
            lntf = lntf + 1
        ENDIF
    ELSE
        lnmc = 1
        lnzrs = 1
        lntf = 1
    ENDIF
    REPLACE pm WITH lnmc, bl WITH lntf
    lczydm = bkzydm
    lnzf = zf
    lnmc = pm
ENDSCAN
BROWSE

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

#3

foolishbir2023-03-01 08:18

以下是引用sdta在2023-2-28 18:05:37的发言：

* VFP9 代码
CLOSE DATABASES
SELECT * FROM DBF\aa ORDER BY bkzydm, zf DESC INTO CURSOR temp READWRITE
BLANK FIELDS pm, bl ALL
lczydm = SPACE(0)
lnzf = 0
lnzrs = 0
SCAN
    lnzrs = lnzrs + 1
    IF bkzydm == lczydm
        IF zf != lnzf
            lnmc = lnzrs
            lntf = 1
        ELSE
            lntf = lntf + 1
        ENDIF
    ELSE
        lnmc = 1
        lnzrs = 1
        lntf = 1
    ENDIF
    REPLACE pm WITH lnmc, bl WITH lntf
    lczydm = bkzydm
    lnzf = zf
    lnmc = pm
ENDSCAN
BROWSE

并列人数这边还是不准确，谢谢

#4

foolishbir2023-03-01 08:29

UPDATE aa SET aa.bl=bb.bl from (sele bkzydm,zf,COUNT(*) as bl from aa grou by bkzydm,zf) as bb WHERE aa.bkzydm=bb.bkzydm AND aa.zf=bb.zf
我加了这句话，把并列重新替换掉了，有没有方法可以在循环中直接处理？

#5

sdta2023-03-01 09:09

程序代码：

* VFP9 代码
CLOSE DATABASES
SELECT * FROM DBF\aa ORDER BY bkzydm, zf DESC INTO CURSOR temp READWRITE
BLANK FIELDS pm, bl ALL
lczydm = SPACE(0)
lnzf = 0
lnzrs = 0
lntf1 = 1
SCAN
    lnzrs = lnzrs + 1
    IF bkzydm == lczydm
        IF zf != lnzf
            lnmc = lnzrs
            IF lntf > 1
                lnrec = RECNO()
                lnrec1 = lnrec - 1
                FOR ln = 1 TO lntf - 1
                    lnrec1 = lnrec1 - 1
                    GO lnrec1
                    REPLACE bl WITH lntf
                ENDFOR
                GO lnrec
                lntf = 1
            ENDIF
        ELSE
            lntf = lntf + 1
        ENDIF
    ELSE
        lnmc = 1
        lnzrs = 1
        lntf = 1
    ENDIF
    REPLACE pm WITH lnmc, bl WITH lntf
    lczydm = bkzydm
    lnzf = zf
    lnmc = pm
ENDSCAN
BROWSE

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

#6

sdta2023-03-01 09:11

以下是引用foolishbir在2023-3-1 08:29:24的发言：

UPDATE aa SET aa.bl=bb.bl from (sele bkzydm,zf,COUNT(*) as bl from aa grou by bkzydm,zf) as bb WHERE aa.bkzydm=bb.bkzydm AND aa.zf=bb.zf
我加了这句话，把并列重新替换掉了，有没有方法可以在循环中直接处理？

实际上没有必要这样，看到BL最大值就知道同分的人数有多少了；如果数据少对速度没有太大的影响，否则影响就大了。

#7

foolishbir2023-03-01 11:24

回复 6楼 sdta

主要是想查看单个学生，就可以看到成绩并列情况，不用再去翻看其他考生，后来改的这个对上了，感谢

#8

吹水佬2023-03-01 11:42

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

程序代码：

SET ENGINEBEHAVIOR 70
SELECT 0000 id, bkzydm, xm, zf, 0000 pm, 0000 bl FROM aa ORDER BY bkzydm, zf DESC INTO CURSOR tmp READWRITE
SELECT bkzydm, zf, COUNT(*) bl FROM tmp GROUP BY bkzydm, zf INTO CURSOR tmp_bl
UPDATE tmp SET id=RECNO(),pm=1,bl=tmp_bl.bl from tmp_bl WHERE tmp.bkzydm==tmp_bl.bkzydm AND tmp.zf==tmp_bl.zf
UPDATE tmp;
SET pm=IIF(tmp.bkzydm!=b.bkzydm, 1, IIF(tmp.zf==b.zf, b.pm, b.pm+b.bl));
from tmp b WHERE b.id==tmp.id-1
SELECT * FROM tmp

#9

吹水佬2023-03-01 14:01

以下是引用吹水佬在2023-3-1 11:42:37的发言：

SET ENGINEBEHAVIOR 70
SELECT 0000 id, bkzydm, xm, zf, 0000 pm, 0000 bl FROM aa ORDER BY bkzydm, zf DESC INTO CURSOR tmp READWRITE
SELECT bkzydm, zf, COUNT(*) bl FROM tmp GROUP BY bkzydm, zf INTO CURSOR tmp_bl
UPDATE tmp SET id=RECNO(),pm=1,bl=tmp_bl.bl from tmp_bl WHERE tmp.bkzydm==tmp_bl.bkzydm AND tmp.zf==tmp_bl.zf
UPDATE tmp;
SET pm=IIF(tmp.bkzydm!=b.bkzydm, 1, IIF(tmp.zf==b.zf, b.pm, b.pm+b.bl));
from tmp b WHERE b.id==tmp.id-1
SELECT * FROM tmp

几行代码看似简单（至少扫描4次），其实效率没5楼的高（明显只用扫描2次）。