dourunlao1642 2018-11-13 09:10
浏览 41
已采纳

mysqli每次工作只有其他条件但不是条件

Here Is My Query

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

Not work

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

Every time work only else condation SUM(gpa)/count(subject_name

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

My Table

enter image description here

Result : gpa = 5+8+4+6

         = 23

But subject_type = 1 so ,minus -2 (not work)

         = 21 (Not work)

Final Gpa = 21/count(subject_name)

  • 写回答

1条回答 默认 最新

  • duanhui3759 2018-11-13 10:01
    关注

    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
    

    I've created a simplified demo at SQLFiddle.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名