php - mysqli每次只能工作其他条件，但如果没有条件

这是我的查询

CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type

不行

IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))

每次仅工作else条件SUM(gpa)/count(subject_name

不起作用subject_type=1,SUM(gpa)-2/count(subject_name)

我的桌子



结果：gpa = 5 + 8 + 4 + 6

         = 23

但是subject_type = 1 so ,minus -2（不起作用）

         = 21 (Not work)

最终Gpa = 21 / count（subject_name）

最佳答案

如果只有一门额外的科目（即具有subject_type=1的科目）而所有其他科目都具有subject_type=0，则可以使用MAX(subject_type)确定学生是否修了一门额外的科目。此查询应执行您想要的操作（请注意，您还需要在()附近使用SUM(gpa)-2）：

CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type

我在SQLFiddle创建了一个简化的演示。

关于php - mysqli每次只能工作其他条件，但如果没有条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53277421/