mysqli每次工作仅在其他条件下,但在没有条件的情况下不工作 [英] mysqli every time work only else condition but not if condition
问题描述
这是我的查询
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
Result : gpa = 5+8+4+6
= 23
但是subject_type = 1 so ,minus -2
(无效)
But subject_type = 1 so ,minus -2
(not work)
= 21 (Not work)
最终Gpa = 21/count(subject_name)
Final Gpa = 21/count(subject_name)
推荐答案
如果只有一个额外的主题(即具有subject_type=1
的主题)而所有其他主题都具有subject_type=0
,则可以使用MAX(subject_type)
确定如果学生选修了额外的科目.该查询应该执行您想要的操作(请注意,您还需要在SUM(gpa)-2
周围加上()
):
If there can be only one extra subject (i.e. subject with subject_type=1
) and all other subjects have subject_type=0
, you can use MAX(subject_type)
to determine if a student took an extra subject. This query should do what you want (note that you also need ()
around 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 中创建了一个简化的演示.
I've created a simplified demo at SQLFiddle.
这篇关于mysqli每次工作仅在其他条件下,但在没有条件的情况下不工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!