MySQL函数：在子句中HAVING查询运行两次

Question

I have written MySQL function which retrieves sum of database records.

SELECT id, myFunction(id) as price from myTable;

returns:

+———————+————————+
| id    |  price |
|  1    |    10  |
|  2    |    20  |
|  3    |    30  |
|  4    |    40  |
|  5    |    50  |
+———————+————————+

A problem occurs while I try to limit results to price BETWEEN 20 AND 40.

If I do it in HAVING CLAUSE, this function will be executed two times. First time while retrieving in SELECT myFunction, and second time near the HAVING PRICE.

Example:

SELECT id, myFunction(id) as price from myTable HAVING price BETWEEN 20 AND 40.

Is it possible to modify this query to one-time execute? Because of complicated calculating, it's bad idea to run this same operation twice.

Answer 1

Use SUM(id) function instead of myFunction(id) because afterall you are just calculating sum of ids, use this query

SELECT id, SUM(id) as price from myTable WHERE price BETWEEN 20 and 40,

Always use having with groupby clause.