Since you can't use a calculated column in a where-clause in MySQL like this:
因为你不能像这样在MySQL中的where子句中使用计算列:
SELECT a,b,c,(a*b+c) AS d FROM table
WHERE d > n
ORDER by d
you have to use
你必须使用
SELECT a,b,c,(a*b+c) AS d FROM table
WHERE (a*b+c) > n
ORDER by d
Is the calculation (in that example "(a*b+c)" executed once per row or twice? Is there a way to make it faster? I find it strange it's possible to ORDER on the column but not to have a WHERE-clause.
计算(在那个例子中是“(a*b+c)”每一行执行一次还是两次?有没有办法让它更快?我觉得奇怪的是，可以在列上订购，但没有where子句。
2 个解决方案
#1
37
You can use HAVING to filter on a computed column:
您可以使用不得不过滤一个计算列:
SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d
Note that you need to include n in the SELECT clause for this to work.
注意，您需要在SELECT子句中包含n才能使其工作。
#2
2
You could do this:
你可以这样做:
SELECT a,b,c,d FROM
(SELECT a,b,c,(a*b+c) AS d) AS tmp
WHERE d > n
ORDER BY d
But I am not sure what performance implications it could have.
但我不确定这对性能有什么影响。
#1
37
You can use HAVING to filter on a computed column:
您可以使用不得不过滤一个计算列:
SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d
Note that you need to include n in the SELECT clause for this to work.
注意，您需要在SELECT子句中包含n才能使其工作。
#2
2
You could do this:
你可以这样做:
SELECT a,b,c,d FROM
(SELECT a,b,c,(a*b+c) AS d) AS tmp
WHERE d > n
ORDER BY d
But I am not sure what performance implications it could have.
但我不确定这对性能有什么影响。