mysql - 在MySQL中使用字段进行操作的最有效方法是什么？

我有以下疑问：

SELECT DATE(utimestamp) as utimestamp, name, data*2000000 from tData
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00'
AND name = 'Valor2' and data>20
group by YEAR(utimestamp), MONTH(utimestamp), name
union
SELECT DATE(utimestamp) as utimestamp, name, data*0.1 from tData
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00'
AND name = 'Valor1' and data>20
group by YEAR(utimestamp), MONTH(utimestamp), name
order by utimestamp asc

有没有更有效的方法来操作“数据”？有没有办法不用工会来做这件事？

最佳答案

您可以尝试使用case when then：

SELECT DATE(utimestamp) as utimestamp, name,
case when name = 'Valor1' then data*0.1
     when name = 'Valor2' then data*2000000
end
from tData
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00'
and data>20
group by YEAR(utimestamp), MONTH(utimestamp), name
order by utimestamp asc