从SQL中的每一行计算2列的平均值 [英] Calculate AVERAGE from 2 columns for each row in SQL

问题描述

作为示例，我将从MySQL获得此表

As an example I will have this table from MySQL

Id  | Name  | Grade 1  | Grade 2 | Average

1.  | Jack  | 9        | 10      |
2.  | Jimmy | 9        | 8       |
2.  | Emmy  | 9        | 7       |

因此，在此表的平均值"字段中，我需要根据 1级和 2级字段计算AVERAGE.我尝试了很多可能是错误的可能性，例如:

So, in the Average field from this table, I need to calculate the AVERAGE from the Grade 1 and Grade 2 fields. I tried a lot of possiblities which I know they are wrong like:

UPDATE table_name SET Average=AVG(Grade 1 + Grade 2)

我有办法做到吗?谁能帮我?谢谢！

I there a way to do this? Can anyone help me? Thanks!

推荐答案

您需要将字段加在一起并除以字段数.如果您的Average字段是DECIMAL类型，则实际上甚至不需要指定ROUND函数.超出声明的任何十进制都将被截断( SQL小提琴 ):

You need to add the fields together and divide by the number of fields. If your Average field is of DECIMAL type you don't really even need to specify the ROUND function. Any decimal exceeding the declaration will just be truncated (SQL Fiddle) :

UPDATE table_name 
SET AVERAGE = (grade1 + grade2) / 2;

在您的示例中，您只有两个要获取平均值的字段.所以Average decimal(3,1)将为您工作，因为最多的小数部分是.5.因此，显然不需要ROUND功能.

In your example you only have two fields that you are getting the average of. So Average decimal(3,1) would work for you since the most the decimal portion will ever be is .5. So the ROUND function is clearly not needed.

这篇关于从SQL中的每一行计算2列的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！