问题描述:

I have following query in mysql:

INSERT INTO table_name(field1, field2, field3, field4......)

SELECT field1, field2,

function_name(field3,'2012-01-01'),

function_name(field3,'2012-01-01')/field1.....

from table_name2 group by field1, field2;

My question is the above query is working fine but as I am using function_name(field3,'2012-01-01') in multiple time so its taking too long time to execute. Now I want i will execute once function_name(field3,'2012-01-01') and will use for field3 and as well as field4. I will not call again function_name for field4 as already I have executed it for field filed3.

So the query should be like this

INSERT INTO table_name(field1, field2, field3,field4......)

SELECT field1, field2,

@res:=function_name(field3,'2012-01-01'),

@res/field1.....

from table_name2

group by field1, field2;

Is it possible?

网友答案:

You can use

INSERT INTO table_name(field1, field2, field3, field4......) SELECT field1, field2, function_name(field3,'2012-01-01') as @res, @res/field1..... from table_name2 group by field1, field2;

网友答案:

Please rechange Query as

"INSERT INTO table_name(field1, field2, field3, field4......) SELECT field1, field2, @res := function_name(field3,'2012-01-01'), @res/field1..... from table_name2 group by field1, field2;

which is same as that of you specified. that is correct I have checked like this

mysql> select ( @point := sum(points)), @point/12  from votes;
+--------------------------+-------------+
| ( @point := sum(points)) | @point/12   |
+--------------------------+-------------+
|                       20 | 1.000000000 |
+--------------------------+-------------+
相关阅读:
Top