MySQL和多列的平均值

I need to return average for each of 12 columns I have in a table in DB. MySQL allows one to get average for one column only. The following query (for one column) works:

我需要为DB中的一个表中的12列中的每一列返回平均值。 MySQL允许只获得一列的平均值。以下查询(对于一列)有效:

SELECT station_id, AVG(jan) AS avg_jan
FROM `climate_data`

WHERE element_name = "Temp_mean_mly" AND jan <> -999999  
GROUP BY station_id

and the following (for multiple columns) does not (I get syntax error):

和以下(对于多列)不(我得到语法错误):

SELECT station_id, AVG(jan) AS avg_jan, AVG(feb) AS avg_feb, ... , 
AVG(dec) AS avg_dec 
FROM `climate_data`

WHERE element_name = "Temp_mean_mly" 
AND jan <> -999999 
AND feb <> -999999
AND ... 
AND dec <> -999999 
GROUP BY station_id

Do I have to use 12 sub-queries to achieve the result I need?

我是否必须使用12个子查询来实现我需要的结果?

Thanks for your help

谢谢你的帮助

1 个解决方案

#1

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dec)

1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'dec'附近使用正确的语法

dec is a reserved MySQL keyword; changing it to `dec` in your query would probably fix that error for you :).

dec是一个保留的MySQL关键字;在查询中将其更改为“dec”可能会为您修复该错误:)。

Edit: note that you're also using it in the WHERE clause; it might work there (as it's unlogical for MySQL to find a keyword there), but keep it in mind that you might also have to escape that one :)

编辑:请注意您也在WHERE子句中使用它;它可能在那里工作(因为MySQL在那里找到一个关键字是不合逻辑的),但请记住,你可能还必须逃避那个:)

#1