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

schtg

来　自：Usa
等　级：贵宾
威　望：67
帖　子：1710
专家分：3318
注　册：2012-2-29

第 11 楼

得分:0

回复 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

2023-10-13 06:44

chychychy

等　级：论坛游民
帖　子：278
专家分：98
注　册：2015-4-18

第 13 楼

得分:0

回复 11楼 schtg

谢谢，对我这样新手，这是比较容易理解的语句模式，运行速度也很快

2023-10-13 08:12

sostemp

等　级：贵宾
威　望：10
帖　子：202
专家分：284
注　册：2009-6-2

第 15 楼

得分:0

以下是引用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就更捧了

2023-10-24 18:28

chychychy

等　级：论坛游民
帖　子：278
专家分：98
注　册：2015-4-18

第 17 楼

得分:0

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

2023-10-25 16:36