php - mysqli 每次只工作 else 条件但不工作 if 条件

Question

这是我的查询

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)

我的 table

成绩:GPA = 5+8+4+6

         = 23

但是 subject_type = 1 所以，减去 -2 (不起作用)

         = 21 (Not work)

最终 Gpa = 21/计数(科目名称)

Answer 1

如果只能有一个额外主题(即具有 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 创建了一个简化的演示.