注册 登录
编程论坛 → VFP论坛

求教：统计各校各科优秀良好合格人数（率）

chychychy 发布于 2023-10-12 11:52， 793 次点击
求教：有成绩表，想统计出各校各学科优秀、良好、及格率（或者人数也可以），但没想出如何用循环方法实现自动统计（虽然做过类似请教，也爬论坛，但是只会逐句用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编辑过]

16 回复
#2
sdta2023-10-12 12:49
优秀、良好、及格的分数段分别是多少
#3
chychychy2023-10-12 13:08
回复 2楼 sdta
优秀是大于等于bz表中的n3，同理良好n2及格n1，比如语文分别是120/105/90

[此贴子已经被作者于2023-10-12 13:11编辑过]
#4
sdta2023-10-12 13:22
用语文举例：
及格的分数段 大于等于90且小于105
良好的分数段 大于等于105且小于120
优秀的分数段 大于等于120
是这个意思吧

[此贴子已经被作者于2023-10-12 13:35编辑过]
#5
schtg2023-10-12 13:26
回复 楼主 chychychy
最简单的方法，一目了然，仅供参考
只有本站会员才能查看附件，请 登录

程序代码：
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编辑过]
#6
chychychy2023-10-12 13:32
回复 4楼 sdta
这个涉及向上包含或者不包含因素，都可以，因为想转换成优秀率、良好率、及格率，所以此处选择的是包含，以语文为例优秀是>=120，良好是>=105，及格是>=90
#7
chychychy2023-10-12 13:35
回复 5楼 schtg
谢谢，学习了，你这个比我最开始的简洁不少，我原来用的是count逐个统计赋值到数组再写回来，但这样通用性不强，如果库结构改变，需要逐句修改。
#8
chychychy2023-10-12 13:38
回复 4楼 sdta
对，这是最准确的方式了，期待高手的思路和解决方式

[此贴子已经被作者于2023-10-12 14:26编辑过]
#9
chychychy2023-10-12 17:31
回复 5楼 schtg
如果是统计优秀率、良好率、及格率应该怎么写，我尝试在你sum语句后面改，没成功
#10
sdta2023-10-12 22:15
程序代码：
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
#11
schtg2023-10-13 06:44
回复 9楼 chychychy
我的计算方法就是那么想就那么算，没有技巧可言哈，可能运算速度不快，以计算出最终结果为目的，纯业余爱好，水平有限
只有本站会员才能查看附件，请 登录

程序代码：
select czxxdm,count(*) as zrs,;
       sum(iif(语文>=120,1,0)) as 优语文,round(sum(iif(语文>=120,1,0))/count(*)*100,2) as 优语文率,;
       sum(iif(语文>=105 and 语文<120,1,0)) as 良语文,round(sum(iif(语文>=105 and 语文<120,1,0))/count(*)*100,2) as 良语文率,;
       sum(iif(语文>=90 and 语文<105,1,0)) as 及语文,round(sum(iif(语文>=90 and 语文<105,1,0))/count(*)*100,2) as 及语文率, ;
       sum(iif(数学>=120,1,0)) as 优数学,round(sum(iif(数学>=120,1,0))/count(*)*100,2) as 优数学率,;
       sum(iif(数学>=105 and 数学<120,1,0)) as 良数学,round(sum(iif(数学>=105 and 数学<120,1,0))/count(*)*100,2) as 良数学率,;
       sum(iif(数学>=90 and 数学<105,1,0)) as 及数学,round(sum(iif(数学>=90 and 数学<105,1,0))/count(*)*100,2) as 及数学率, ;
       sum(iif(英语>=120,1,0)) as 优英语,round(sum(iif(英语>=120,1,0))/count(*)*100,2) as 优英语率,;
       sum(iif(英语>=105 and 英语<120,1,0)) as 良英语,round(sum(iif(英语>=105 and 英语<120,1,0))/count(*)*100,2) as 良英语率,;
       sum(iif(英语>=90 and 英语<105,1,0)) as 及英语,round(sum(iif(英语>=90 and 英语<105,1,0))/count(*)*100,2) as 及英语率, ;
       sum(iif(物理>=80,1,0)) as 优物理,round(sum(iif(物理>=80,1,0))/count(*)*100,2) as 优物理率,;
       sum(iif(物理>=70 and 物理<80,1,0)) as 良物理,round(sum(iif(物理>=70 and 物理<80,1,0))/count(*)*100,2) as 良物理率,;
       sum(iif(物理>=60 and 物理<70,1,0)) as 及物理,round(sum(iif(物理>=60 and 物理<70,1,0))/count(*)*100,2) as 及物理率, ;
       sum(iif(化学>=80,1,0)) as 优化学,round(sum(iif(化学>=80,1,0))/count(*)*100,2) as 优化学率,;
       sum(iif(化学>=70 and 化学<80,1,0)) as 良化学,round(sum(iif(化学>=70 and 化学<80,1,0))/count(*)*100,2) as 良化学率,;
       sum(iif(化学>=60 and 化学<70,1,0)) as 及化学,round(sum(iif(化学>=60 and 化学<70,1,0))/count(*)*100,2) as 及化学率, ;
       sum(iif(生物>=80,1,0)) as 优生物,round(sum(iif(生物>=80,1,0))/count(*)*100,2) as 优生物率,;
       sum(iif(生物>=70 and 生物<80,1,0)) as 良生物,round(sum(iif(生物>=70 and 生物<80,1,0))/count(*)*100,2) as 良生物率,;
       sum(iif(生物>=60 and 生物<70,1,0)) as 及生物,round(sum(iif(生物>=60 and 生物<70,1,0))/count(*)*100,2) as 及生物率, ;
       sum(iif(政治>=80,1,0)) as 优政治,round(sum(iif(政治>=80,1,0))/count(*)*100,2) as 优政治率,;
       sum(iif(政治>=70 and 政治<80,1,0)) as 良政治,round(sum(iif(政治>=70 and 政治<80,1,0))/count(*)*100,2) as 良政治率,;
       sum(iif(政治>=60 and 政治<70,1,0)) as 及政治,round(sum(iif(政治>=60 and 政治<70,1,0))/count(*)*100,2) as 及政治率, ;
       sum(iif(历史>=80,1,0)) as 优历史,round(sum(iif(历史>=80,1,0))/count(*)*100,2) as 优历史率,;
       sum(iif(历史>=70 and 历史<80,1,0)) as 良历史,round(sum(iif(历史>=70 and 历史<80,1,0))/count(*)*100,2) as 良历史率,;
       sum(iif(历史>=60 and 历史<70,1,0)) as 及历史,round(sum(iif(历史>=60 and 历史<70,1,0))/count(*)*100,2) as 及历史率, ;
       sum(iif(地理>=80,1,0)) as 优地理,round(sum(iif(地理>=80,1,0))/count(*)*100,2) as 优地理率,;
       sum(iif(地理>=70 and 地理<80,1,0)) as 良地理,round(sum(iif(地理>=70 and 地理<80,1,0))/count(*)*100,2) as 良地理率,;
       sum(iif(地理>=60 and 地理<70,1,0)) as 及地理,round(sum(iif(地理>=60 and 地理<70,1,0))/count(*)*100,2) as 及地理率 ;
       from zcj group by czxxdm order by czxxdm
#12
chychychy2023-10-13 08:11
回复 10楼 sdta
版主的思维的逻辑性太强了，谢谢，不过语句对我有点复杂，慢慢学习消化。
#13
chychychy2023-10-13 08:12
回复 11楼 schtg
谢谢，对我这样新手，这是比较容易理解的语句模式，运行速度也很快
#14
chychychy2023-10-13 09:14
@sdta@sdta感谢两位帮助，结贴时点击错误了，请见谅
#15
sostemp2023-10-24 18:28
以下是引用schtg在2023-10-13 06:44:56的发言：

我的计算方法就是那么想就那么算，没有技巧可言哈，可能运算速度不快，以计算出最终结果为目的，纯业余爱好，水平有限

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


这SQL好长呀，且是固定的。其实给定学科及学科优良合分数，动态生成SQL就更捧了
#16
dbf永动机2023-10-25 10:00
回复 15楼 sostemp
穷则火力覆盖，富则精准打击，简单粗暴就完事了
#17
chychychy2023-10-25 16:36
回复 15楼 sostemp
已给定了各自分数段，>=n1是及格，>=n2为良好，>=n3为优秀
*--*创建各科优秀良好及格段次表
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)

[此贴子已经被作者于2023-10-25 16:38编辑过]
1
©2024, BCCN.NET
电脑版