可以在WHERE子句中使用MySQL临时变量吗? [英] can MySQL temporary variables be used in WHERE clause?

问题描述

在MySQL中，可以在WHERE子句中使用临时变量吗?

In MySQL, can temporary variables be used in the WHERE clause?

例如，在以下查询中:

SELECT `id`, @var := `id` * 2 FROM `user`

@var已成功设置为`id`值的两倍

@var is successfully set to twice the value of `id`

但是，如果我尝试过滤结果集以仅包含@var小于10的结果:

However, if I try to filter the result set to only include results where @var is less than 10:

SELECT `id`, @var := `id` * 2 FROM `user` WHERE @var < 10

然后我没有结果.

如何根据@var的值过滤结果?

How can I filter the results based on the value of @var?

推荐答案

您需要分配一个别名，并在HAVING子句中对其进行测试:

You need to assign an alias, and test it in the HAVING clause:

SELECT id, @var := id * 2 AS id_times_2
FROM user
HAVING id_times_2 < 10

请注意，如果仅使用公式进行过滤，而不是将内部结果从一行传递到另一行，则根本不需要该变量.您可以编写:

Note that if you're just using the formula for filtering, not for communicating internal results from one row to the next, you don't need the variable at all. You can write:

SELECT id, id * 2 AS id_times_2
FROM user
HAVING id_times_2 < 10

这篇关于可以在WHERE子句中使用MySQL临时变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！