<求助>SQL统计各班各科不及格的人数 - 编程论坛

#2

laowan0012024-04-24 10:48

以下是引用ycvf在2024-4-24 10:35:46的发言：

各科成绩不合格的值分别为 M 或缺考或空值，班级(bj)为4个班（bj1,bj2,bj3,bj4),分别统计每个班的语文，数学，外语不合格中M、缺考、空值的数。
sele bj,count(*) 语文 ,count(*) 数学,count(*) 外语 from g:\bj.dbf wher 语文="M" or 数学="M" or 外语="M" group by bj

这个句子的结果是三个统计值是一样的

可以分别统计，然后组合成一个查询结果
select a.bj,b.语文不及格人数,c.数学不及格人数,d.外语不及格人数
from (select distinct bj FROM g:\bj.dbf) a
left join (select bj,count(*) 语文不及格人数 from g:\bj.dbf where 语文="M" group by bj) b ON b.bj=a.bj
left join (select bj,count(*) 数学不及格人数 from g:\bj.dbf where 数学="M" group by bj) c ON c.bj=a.bj
left join (select bj,count(*) 外语不及格人数 from g:\bj.dbf where 外语="M" group by bj) d ON d.bj=a.bj

#3

sdta2024-04-24 11:16

SELECT Bj, ;
SUM(IIF(ALLTRIM(语文)=="M",1,0)) 语文M, SUM(IIF(ALLTRIM(语文)=="缺考",1,0)) 语文缺考,  SUM(IIF(ALLTRIM(语文)=="空值",1,0)) 语文空值, ;
SUM(IIF(ALLTRIM(数学)=="M",1,0)) 数学M, SUM(IIF(ALLTRIM(数学)=="缺考",1,0)) 数学缺考,  SUM(IIF(ALLTRIM(数学)=="空值",1,0)) 数学空值, ;
SUM(IIF(ALLTRIM(外语)=="M",1,0)) 外语M, SUM(IIF(ALLTRIM(外语)=="缺考",1,0)) 外语缺考,  SUM(IIF(ALLTRIM(外语)=="空值",1,0)) 外语空值, ;
FROM g:\bj GROUP BY Bj

如果要三类人员的合计数代码如下
SELECT Bj, ;
SUM(IIF(ALLTRIM(语文)=="M",1,0)) + SUM(IIF(ALLTRIM(语文)=="缺考",1,0)) + SUM(IIF(ALLTRIM(语文)=="空值",1,0)) 语文M缺空, ;
SUM(IIF(ALLTRIM(数学)=="M",1,0)) + SUM(IIF(ALLTRIM(数学)=="缺考",1,0)) + SUM(IIF(ALLTRIM(数学)=="空值",1,0)) 数学M缺空, ;
SUM(IIF(ALLTRIM(外语)=="M",1,0)) + SUM(IIF(ALLTRIM(外语)=="缺考",1,0)) + SUM(IIF(ALLTRIM(外语)=="空值",1,0)) 外语M缺空, ;
FROM g:\bj GROUP BY Bj

[此贴子已经被作者于2024-4-24 11:22编辑过]

#4

yiyanxiyin2024-04-24 14:23

sele bj 班级,"语文" 科目, count(*) 不及格人数  from g:\bj.dbf where 语文 in("M","缺考","空置")   group by bj, 语文
union all
sele bj 班级,"数学" 科目, count(*) 不及格人数   from g:\bj.dbf where 数学 in("M","缺考","空置")     group by bj, 数学
union all
sele bj 班级,"外语" 科目, count(*) 不及格人数   from g:\bj.dbf where 外语 in("M","缺考","空置")     group by bj, 外语