在MySQL中计算z值

I have a table, gainfinal, which consists of three columns: countrycode, year and values.

I want to calculate z values for all the rows. I did a simple query like this.

SELECT (
 `values` - STDDEV(  `values` )
) / AVG(  `values` ) AS zvalue
FROM  `gainfinal`

But the query returned only one row. How can I perform same thing for all the rows?

You need a subquery to get the aggregated values:

SELECT (gf.`values` - stats.average) / stats.s AS zvalue
FROM  `gainfinal` gf cross join
      (select sttdev(values) as s, avg(values) as average from gainfinal) stats;

In most databases, your original query would generate an error, because it is mixing aggregated columns and non-aggregated columns in a query with no group by. The solution is a separate subquery that does the calculation.

How about this?

DECLARE @stdev AS FLOAT, @avg AS FLOAT

SELECT @stdev = STDEV([values]), @avg = AVG([values])
FROM gainfinal

SELECT 
    countrycode
    , year
    , [values]
    , ([values] - @stdev) / @avg AS zvalue
FROM gainfinal