求教：有成绩表，想统计出各校各学科优秀、良好、及格率（或者人数也可以），但没想出如何用循环方法实现自动统计（虽然做过类似请教，也爬论坛，但是只会逐句用count统计，没学会简洁的循环语句统计）

*--*创建各科优秀良好及格段次表
CREATE CURSOR bz (km c(10), n1 N(6, 2), n2 N(6, 2), n3 N(6, 2))
INSERT INTO bz VALUES ("语文", 90,105,120)
INSERT INTO bz VALUES ("数学", 90,105,120)
INSERT INTO bz VALUES ("英语", 90,105,120)
INSERT INTO bz VALUES ("物理", 60,70,80)
INSERT INTO bz VALUES ("化学", 60,70,80)
INSERT INTO bz VALUES ("政治", 60,70,80)
INSERT INTO bz VALUES ("历史", 60,70,80)
INSERT INTO bz VALUES ("地理", 60,70,80)
INSERT INTO bz VALUES ("生物", 60,70,80)
&&&BROWSE
SELECT n1, n2,n3 FROM bz INTO ARRAY abz
USE zcj IN 0
*--*创建统计表结构
SELECT km  FROM bz INTO ARRAY arr_km
SELECT DISTINCT czxxdm FROM zcj ORDER BY czxxdm INTO ARRAY arr_czxxdm
cmd = "CREATE CURSOR tt (czxxdm c(8),zrs I"
FOR i=1 TO ALEN(arr_km)
    cmd = cmd + ","  + "优" + arr_km[i] + " n(6,2)" + ","  + "良" + arr_km[i] + " n(6,2)" + ","  + "及" + arr_km[i] + " n(6,2)"
ENDFOR
cmd = cmd + ")"
EXECSCRIPT(cmd)
APPEND FROM ARRAY arr_czxxdm
BROWSE
*--*计算统计各校各科优秀良好合格人数？？

[此贴子已经被作者于2023-10-12 12:49编辑过]

优秀、良好、及格的分数段分别是多少

优秀是大于等于bz表中的n3，同理良好n2及格n1，比如语文分别是120/105/90

[此贴子已经被作者于2023-10-12 13:11编辑过]

用语文举例：
及格的分数段 大于等于90且小于105
良好的分数段 大于等于105且小于120
优秀的分数段 大于等于120
是这个意思吧

[此贴子已经被作者于2023-10-12 13:35编辑过]

最简单的方法，一目了然，仅供参考

select czxxdm,count(*) as zrs,;
       sum(iif(语文>=120,1,0)) as 优语文,sum(iif(语文>=105 and 语文<120,1,0)) as 良语文,sum(iif(语文>=90 and 语文<105,1,0)) as 及语文, ;
       sum(iif(数学>=120,1,0)) as 优数学,sum(iif(数学>=105 and 数学<120,1,0)) as 良数学,sum(iif(数学>=90 and 数学<105,1,0)) as 及数学, ;
       sum(iif(英语>=120,1,0)) as 优英语,sum(iif(英语>=105 and 英语<120,1,0)) as 良英语,sum(iif(英语>=90 and 英语<105,1,0)) as 及英语, ;
       sum(iif(物理>=80,1,0)) as 优物理,sum(iif(物理>=70 and 物理<80,1,0)) as 良物理,sum(iif(物理>=60 and 物理<70,1,0)) as 及物理, ; 
       sum(iif(化学>=80,1,0)) as 优化学,sum(iif(化学>=70 and 化学<80,1,0)) as 良化学,sum(iif(化学>=60 and 化学<70,1,0)) as 及化学, ;
       sum(iif(生物>=80,1,0)) as 优生物,sum(iif(生物>=70 and 生物<80,1,0)) as 良生物,sum(iif(生物>=60 and 生物<70,1,0)) as 及生物, ;
       sum(iif(政治>=80,1,0)) as 优政治,sum(iif(政治>=70 and 政治<80,1,0)) as 良政治,sum(iif(政治>=60 and 政治<70,1,0)) as 及政治, ; 
       sum(iif(历史>=80,1,0)) as 优历史,sum(iif(历史>=70 and 历史<80,1,0)) as 良历史,sum(iif(历史>=60 and 历史<70,1,0)) as 及历史, ;
       sum(iif(地理>=80,1,0)) as 优地理,sum(iif(地理>=70 and 地理<80,1,0)) as 良地理,sum(iif(地理>=60 and 地理<70,1,0)) as 及地理 ;
       from zcj group by czxxdm order by czxxdm

[此贴子已经被作者于2023-10-12 13:27编辑过]

这个涉及向上包含或者不包含因素，都可以，因为想转换成优秀率、良好率、及格率，所以此处选择的是包含，以语文为例优秀是>=120，良好是>=105，及格是>=90

谢谢，学习了，你这个比我最开始的简洁不少，我原来用的是count逐个统计赋值到数组再写回来，但这样通用性不强，如果库结构改变，需要逐句修改。

对，这是最准确的方式了，期待高手的思路和解决方式

[此贴子已经被作者于2023-10-12 14:26编辑过]

如果是统计优秀率、良好率、及格率应该怎么写，我尝试在你sum语句后面改，没成功

CLOSE DATABASES 
CREATE CURSOR bz (km c(10), n1 N(6, 2), n2 N(6, 2), n3 N(6, 2))
INSERT INTO bz VALUES ("语文", 90,105,120)
INSERT INTO bz VALUES ("数学", 90,105,120)
INSERT INTO bz VALUES ("英语", 90,105,120)
INSERT INTO bz VALUES ("物理", 60,70,80)
INSERT INTO bz VALUES ("化学", 60,70,80)
INSERT INTO bz VALUES ("政治", 60,70,80)
INSERT INTO bz VALUES ("历史", 60,70,80)
INSERT INTO bz VALUES ("地理", 60,70,80)
INSERT INTO bz VALUES ("生物", 60,70,80)
* 创建统计表结构
lcStr = "czxxdm c(8), zrs n(6)"
SCAN 
    lcStr = lcStr + ", " + ALLTRIM(km) + "_优1 N(4), " + ALLTRIM(km) + "_优2 C(6)" + ", " + ALLTRIM(km) + "_良1 N(4), " + ALLTRIM(km) + "_良2 C(6)" + ", " + ALLTRIM(km) + "_及1 N(4), " + ALLTRIM(km) + "_及2 C(6)"
ENDSCAN
CREATE CURSOR tj (&lcStr)
INSERT INTO tj (czxxdm, zrs) SELECT czxxdm, COUNT(*) FROM zcj GROUP BY czxxdm 
INDEX ON czxxdm TAG dm 
* 计算各科符合条件的人数
SELECT bz
SCAN
    lckm = ALLTRIM(km)
    SELECT DISTINCT czxxdm FROM zcj INTO CURSOR xxdm
    SCAN
        SELECT czxxdm, EVALUATE(lckm) &lckm, SPACE(2) 等级 FROM zcj WHERE czxxdm == xxdm.czxxdm INTO CURSOR temp READWRITE 
        REPLACE 等级 WITH ICASE(&lckm < bz.n1, SPACE(0), &lckm >= bz.n1 AND &lckm < bz.n2, "及", &lckm >= bz.n2 AND &lckm < bz.n3, "良", "优") ALL 
        SET RELATION TO czxxdm INTO tj
        SCAN FOR NOT EMPTY(等级)
            REPLACE (lckm + "_" + temp.等级 + "1") WITH EVALUATE(lckm + "_" + temp.等级 + "1") + 1 IN tj
        ENDSCAN
        SET RELATION TO
    ENDSCAN
ENDSCAN 
* 计算优秀（、良好、及格）率
SELECT bz 
SCAN 
    SELECT tj
    REPLACE (ALLTRIM(bz.km) + "_优2") WITH IIF(EMPTY(EVALUATE(ALLTRIM(bz.km) + "_优1")), SPACE(0), STR(EVALUATE(ALLTRIM(bz.km) + "_优1")*100/zrs,6,2)) ALL 
    REPLACE (ALLTRIM(bz.km) + "_良2") WITH IIF(EMPTY(EVALUATE(ALLTRIM(bz.km) + "_良1")), SPACE(0), STR(EVALUATE(ALLTRIM(bz.km) + "_良1")*100/zrs,6,2)) ALL 
    REPLACE (ALLTRIM(bz.km) + "_及2") WITH IIF(EMPTY(EVALUATE(ALLTRIM(bz.km) + "_及1")), SPACE(0), STR(EVALUATE(ALLTRIM(bz.km) + "_及1")*100/zrs,6,2)) ALL 
ENDSCAN

SELECT tj
BROWSE