mysql - MySQL获取平均值

这个问题使我头晕。
我有这样的学生：

[teac_id] [less_id] [cl_id] [stu_id] [semester] [nilai_1] [nilai_2] [nilai_3] [nilai_4]
   3         3         1        1         1       90.00     90.00     90.00     null
   3         3         1        2         1       70.00     100.00    null      null

我使用复合主键，问题是如何得到这样的结果：

[teac_id] [less_id] [cl_id] [stu_id] [semester] [ AVG ]
   3         3         1       1        1         (nilai_1 + nilai_2 + nilai_3) / 3
   3         3         1       2        1         (nilai_1 + nilai_2) / 2

字段没有值不能是除数。
谢谢提前。

最佳答案

如果numbernilai_字段是固定的（即您有四个nilai_1..nilai_4），则可以尝试类似的操作（请注意，至少有一个字段不应为空）：

select teac_id,
       less_id,
       cl_id,
       stu_id,
       semester,
       -- average: just sum / count
       (IfNull(nilai_1, 0) +
        IfNull(nilai_2, 0) +
        IfNull(nilai_3, 0) +
        IfNull(nilai_4, 0)) /
        (if(nilai_1 is null, 0, 1) +
         if(nilai_2 is null, 0, 1) +
         if(nilai_3 is null, 0, 1) +
         if(nilai_4 is null, 0, 1)) as AVG
  from MyTable

如果一条记录中的所有nilai_1..nilai_4都可以为空，则必须修改查询：

select teac_id,
       less_id,
       cl_id,
       stu_id,
       semester,

       case
         when (nilai_1 is null) and
              (nilai_2 is null) and
              (nilai_3 is null) and
              (nilai_4 is null) then
           -- special case: all nulls average
           null
         else
           -- average: just sum / count
          (IfNull(nilai_1, 0) +
           IfNull(nilai_2, 0) +
           IfNull(nilai_3, 0) +
           IfNull(nilai_4, 0)) /
             (if(nilai_1 is null, 0, 1) +
              if(nilai_2 is null, 0, 1) +
              if(nilai_3 is null, 0, 1) +
              if(nilai_4 is null, 0, 1))
       end as AVG
  from MyTable

关于mysql - MySQL获取平均值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23831891/