mysql - MySQL查询中动态生成字段的总和

Question

这是我对动态计算字段的查询:

SELECT 5 * (`Mean (all)` +0.01735568 )/(0.07268901) AS rank1, 
5 * (`CVaR 95` +0.51612 )/(0.53212) AS rank2, 
5 * (`Hurst` - 0.2 )/(0.7717653) AS rank3, 
5 * (`MaxDD` +6.200000762939453 )/(16.200000762939) AS rank4, 
5 * (`Positive % 12` - 0.3 )/(1) AS rank5
FROM `quant1`

它工作正常，但我需要添加另一个等于 rank1+rank2...+rank 5 的动态字段。因此，我添加了另一个字段，例如

SELECT 5 * (`Mean (all)` +0.01735568 )/(0.07268901) AS rank1, 
5 * (`CVaR 95` +0.51612 )/(0.53212) AS rank2, 
5 * (`Hurst` - 0.2 )/(0.7717653) AS rank3, 
5 * (`MaxDD` +6.200000762939453 )/(16.200000762939) AS rank4, 
5 * (`Positive % 12` - 0.3 )/(1) AS rank5,
rank1+rank2+rank3+rank4+rank5 AS rank
FROM `quant1`

它生成错误“未知列 rank1”。有趣的是，完全相同的查询在 MS Access 上运行良好。

我该如何解决这个问题？

谢谢!

Answer 1

在 MySQL 中，你不能引用其他别名，但你可以在 select 中定义变量，所以试试这个:

SELECT
   @rank1 := 5 * (`Mean (all)` +0.01735568 )/(0.07268901) AS rank1, 
   @rank2 := 5 * (`CVaR 95` +0.51612 )/(0.53212) AS rank2, 
   @rank3 := 5 * (`Hurst` - 0.2 )/(0.7717653) AS rank3, 
   @rank4 := 5 * (`MaxDD` +6.200000762939453 )/(16.200000762939) AS rank4, 
   @rank5 := 5 * (`Positive % 12` - 0.3 )/(1) AS rank5,
   @rank1 + @rank2 + @rank3 + @rank4 + @rank5 AS rank
FROM `quant1`

它很丑，但它有效。