MySQL获取平均值

Question

这个问题让我头晕目眩。我有这样的表学生:

[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

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

Answer 1

如果数字 nilai_ 字段是固定的(即你有四个 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