mysqli每次只在else条件下工作，而不在if条件下工作

j2qf4p5b 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(295)

这是我的问题

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

但主题类型=1 so ,minus -2 （不工作）

= 21 (Not work)

最终gpa=21/计数（受试者姓名）

mysql php mysqli codeigniter

来源：https://stackoverflow.com/questions/53277421/mysqli-every-time-work-only-else-condition-but-not-if-condition

1条答案

按热度按时间

mzillmmw1#

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

赞(0）回复(0）举报 2021-06-18

我来回答

mysqli每次只在else条件下工作，而不在if条件下工作

1条答案

相关问题

热门标签

最新问答