各位，另起一题，请教可否还有更快一点的算法，希望缩短执行时间。
从表中的 yw 字段开始以后的全部字段均要分别计算：一是全体考生的平均分，二是以地市代码dsdm为准的地市平均分，三是以县区代码xqdm为准的县区平均分，四是以学校代码xxdm为准的学校平均分(0分除外)，还需要一部分名次，样表格式如下：

数据表：https://down.bccn.net/12536.html
我使用 select avg()计算，需要较长时间才可以完成，希望得到大侠们的帮助，可否寻找到更优的计算方法，谢谢！

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

给代码看看

这是计算平均分的(排序在前一题中)，我自己的代码纯属胡编乱造的，勿笑

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

每一个 SELECT ...SQL要扫描一次，加上循环调用就更慢。
试算了一下ywpjf和sxpjf（其他加上去就是），剩下是算名次。

CREATE CURSOR pjf (dsdm n(6),xqdm n(8),xxdm n(10),ckzrs I,;
                   ywpjf n(12,2),ywlkpx n(12,2),ywbspx n(12,2),ywbxpx n(12,2),;
                   sxpjf n(12,2),sxlkpx n(12,2),sxbspx n(12,2),sxbxpx n(12,2);
                   )
                   
SELECT DISTINCT dsdm FROM cj ORDER BY dsdm INTO CURSOR tmp_dsdm                   
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_dsdm READWRITE 
APPEND FROM DBF("tmp_dsdm")
INDEX on dsdm TAG tag_dsdm

SELECT DISTINCT xqdm FROM cj ORDER BY xqdm INTO CURSOR tmp_xqdm
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_xqdm READWRITE
APPEND FROM DBF("tmp_xqdm") 
INDEX on xqdm TAG tag_xqdm

SELECT DISTINCT xxdm FROM cj ORDER BY xxdm INTO CURSOR tmp_xxdm
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_xxdm READWRITE
APPEND FROM DBF("tmp_xxdm")
INDEX on xxdm TAG tag_xxdm

INSERT INTO pjf (dsdm,ckzrs) VALUES (0,RECCOUNT("cj"))

SELECT cj
SET RELATION TO dsdm INTO "pjf_dsdm", xqdm INTO "pjf_xqdm", xxdm INTO "pjf_xxdm" 
REPLACE ALL pjf.ywpjf      WITH pjf.ywpjf      + cj.yw,;
            pjf.sxpjf      WITH pjf.sxpjf      + cj.sx,;
            pjf_dsdm.ckzrs WITH pjf_dsdm.ckzrs + 1,;
            pjf_dsdm.ywpjf WITH pjf_dsdm.ywpjf + cj.yw,;
            pjf_dsdm.sxpjf WITH pjf_dsdm.sxpjf + cj.sx,;
            pjf_xqdm.ckzrs WITH pjf_xqdm.ckzrs + 1,;
            pjf_xqdm.ywpjf WITH pjf_xqdm.ywpjf + cj.yw,;
            pjf_xqdm.sxpjf WITH pjf_xqdm.sxpjf + cj.sx,;
            pjf_xxdm.ckzrs WITH pjf_xxdm.ckzrs + 1,;
            pjf_xxdm.ywpjf WITH pjf_xxdm.ywpjf + cj.yw,;
            pjf_xxdm.sxpjf WITH pjf_xxdm.sxpjf + cj.sx

SELECT pjf 
APPEND FROM DBF("pjf_dsdm")
APPEND FROM DBF("pjf_xqdm")
APPEND FROM DBF("pjf_xxdm")
REPLACE ALL ywpjf WITH ywpjf/ckzrs,;
            sxpjf WITH sxpjf/ckzrs

SELECT * FROM pjf

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

谢谢！吹版主。

试了一下，算量还不少，i3的机跑8秒左右
数据未校对

tm=SECONDS()

USE cj
DIMENSION afs[FCOUNT()-5]    && yw及之后的字段名列表
FOR i=6 TO FCOUNT()
     afs[i-5] = FIELD(i)
ENDFOR

    * 创建平均分表
cmd = "CREATE CURSOR pjf (dsdm n(6),xqdm n(8),xxdm n(10),ckzrs I"
FOR i=1 TO ALEN(afs)
    cmd = cmd + "," + afs[i]+"pjf n(12,2)," + afs[i]+"lkpx I," + afs[i]+"bspx I," + afs[i]+"bxpx I"
ENDFOR
cmd = cmd + ")"
EXECSCRIPT(cmd)

    * 从 cj 获取 xxdm 的人数和分数
SELECT DISTINCT dsdm,xqdm,xxdm FROM cj ORDER BY dsdm,xqdm,xxdm INTO CURSOR tmp
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_xxdm READWRITE
APPEND FROM DBF("tmp")
INDEX on xxdm TAG tag_xxdm
SELECT cj
SET RELATION TO xxdm INTO "pjf_xxdm" 
cmd = "REPLACE ALL pjf_xxdm.ckzrs WITH pjf_xxdm.ckzrs+1"
FOR i=1 TO ALEN(afs)
    cmd = cmd + ",pjf_xxdm." + afs[i] + "pjf WITH pjf_xxdm." + afs[i] + "pjf + cj." + afs[i]
ENDFOR
EXECSCRIPT(cmd)
SET RELATION TO

    * 从 pjf_xxdm 获取 xqdm 的人数和分数
SELECT DISTINCT dsdm,xqdm FROM pjf_xxdm ORDER BY dsdm,xqdm INTO CURSOR tmp
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_xqdm READWRITE
APPEND FROM DBF("tmp")
INDEX on xqdm TAG tag_xqdm
SELECT pjf_xxdm
SET RELATION TO xqdm INTO "pjf_xqdm" 
cmd = "REPLACE ALL pjf_xqdm.ckzrs WITH pjf_xqdm.ckzrs + pjf_xxdm.ckzrs"
FOR i=1 TO ALEN(afs)
    cmd = cmd + ",pjf_xqdm." + afs[i] + "pjf WITH pjf_xqdm." + afs[i] + "pjf + pjf_xxdm." + afs[i] + "pjf"
ENDFOR
EXECSCRIPT(cmd)
SET RELATION TO

    * 从 pjf_xqdm 获取 dsdm 的人数和分数
SELECT DISTINCT dsdm FROM pjf_xqdm ORDER BY dsdm INTO CURSOR tmp
SELECT * FROM pjf WHERE .t. INTO CURSOR pjf_dsdm READWRITE
APPEND FROM DBF("tmp")
INDEX on dsdm TAG tag_dsdm
SELECT pjf_xqdm
SET RELATION TO dsdm INTO "pjf_dsdm" 
cmd = "REPLACE ALL pjf_dsdm.ckzrs WITH pjf_dsdm.ckzrs + pjf_xqdm.ckzrs"
FOR i=1 TO ALEN(afs)
    cmd = cmd + ",pjf_dsdm." + afs[i] + "pjf WITH pjf_dsdm." + afs[i] + "pjf + pjf_xqdm." + afs[i] + "pjf"
ENDFOR
EXECSCRIPT(cmd)
SET RELATION TO

    * 从 pjf_dsdm 获取总人数和总分数
INSERT INTO pjf (dsdm) VALUES (0)
SELECT pjf_dsdm
cmd = "REPLACE ALL pjf.ckzrs WITH pjf.ckzrs + pjf_dsdm.ckzrs"
FOR i=1 TO ALEN(afs)
    cmd = cmd + ",pjf." + afs[i] + "pjf WITH pjf." + afs[i] + "pjf + pjf_dsdm." + afs[i] + "pjf"
ENDFOR
EXECSCRIPT(cmd)

    * 统计 各类平均分数
tj_pjf("pjf")
tj_pjf("pjf_dsdm")
tj_pjf("pjf_xqdm")
tj_pjf("pjf_xxdm")

    * 统计 联考名次、本市名次、本县名次
tj_mcs("xqdm")
tj_mcs("xxdm")

    * 整合各类表
SELECT pjf 
APPEND FROM DBF("pjf_dsdm")
APPEND FROM DBF("pjf_xqdm")
APPEND FROM DBF("pjf_xxdm")

? SECONDS()-tm
SELECT * FROM pjf 
CLOSE TABLES ALL 
RETURN

    * 统计平均分
FUNCTION tj_pjf(cAlias)
    cmd = ""
    FOR i=1 TO ALEN(afs)
        cmd = cmd + "," + afs[i] + "pjf WITH " + afs[i] + "pjf/ckzrs"
    ENDFOR
    cmd = "REPLACE ALL " + SUBSTR(cmd,2) + " IN " + cAlias
    EXECSCRIPT(cmd)
ENDFUNC

    * 统计 联考名次、本市名次、本县名次
FUNCTION tj_mcs(dmm)
    FOR i=1 TO ALEN(afs)
        tj_mc(dmm, afs[i])
    ENDFOR
ENDFUNC

    * 统计x类x科 联考名次、本市名次、本县名次
FUNCTION tj_mc(dmm, kmm)
        * 联考名次
    cmd = "SELECT " + dmm + "," + kmm + "pjf FROM pjf_" + dmm + " ORDER BY " + kmm + "pjf DESC INTO CURSOR tmp"
    EXECSCRIPT(cmd)
    EXECSCRIPT("SET RELATION TO " + dmm + " INTO pjf_" + dmm) 
    m = 1
    n = 1
    fs = 0
    SCAN 
        IF EVALUATE(kmm+"pjf") != fs
            fs = EVALUATE(kmm+"pjf")
            n = m
        ENDIF
        REPLACE ("pjf_" + dmm + "." + kmm + "lkpx") WITH n 
        m = m + 1
    ENDSCAN
    SET RELATION TO
    USE IN "tmp"
        * 本市名次
    cmd = "SELECT dsdm," + dmm + "," + kmm + "pjf FROM pjf_" + dmm + " ORDER BY dsdm," + kmm + "pjf DESC INTO CURSOR tmp"
    EXECSCRIPT(cmd)
    EXECSCRIPT("SET RELATION TO " + dmm + " INTO pjf_" + dmm)  
    m = 1
    n = 1
    dm = 0
    fs = 0
    SCAN 
        IF dm != dsdm
            m = 1
            n = 1
            dm = dsdm
            fs = EVALUATE(kmm+"pjf")
        ENDIF 
        IF EVALUATE(kmm+"pjf") != fs
            fs = EVALUATE(kmm+"pjf")
            n = m
        ENDIF
        REPLACE ("pjf_" + dmm + "." + kmm + "bspx") WITH n 
        m = m + 1
    ENDSCAN
    SET RELATION TO
    USE IN "tmp"
   
    IF dmm != "xxdm"  
        RETURN
    ENDIF
        * 本县名次
    cmd = "SELECT dsdm,xqdm,xxdm," + kmm + "pjf FROM pjf_xxdm ORDER BY xqdm," + kmm + "pjf DESC INTO CURSOR tmp"
    EXECSCRIPT(cmd)
    SET RELATION TO xxdm INTO pjf_xxdm
    m = 1
    n = 1
    dm = 0
    fs = 0
    SCAN
        IF dm != xqdm
            m = 1
            n = 1
            dm = xqdm
            fs = EVALUATE(kmm+"pjf")
        ENDIF  
        IF EVALUATE(kmm+"pjf") != fs
            fs = EVALUATE(kmm+"pjf")
            n = m
        ENDIF
        REPLACE ("pjf_xxdm." + kmm + "bxpx") WITH n
        m = m + 1
    ENDSCAN
ENDFUNC

吹版主，非常感谢！谢谢你的大力赐教！
我们计算平均分时，一般不将零分计算其中，要把零分人数从总人数中减去。

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

吹版主，请问如果计算平均分时排除 零分，如何修改为佳？谢谢！

开始或最后结果全0的记录可以去掉，其中有一不是0的记录也去不了吧

我们时按照单学科来计算平均分的，计算语文平均分时，语文成绩为0的学生不计入总人数中，即语文分数总和/语文成绩大于0的学生总人数。如果用select avg()表示的话，就是：select avg(yw) as ywpjf from tablename where yw > 0。不知我讲明白了吗