MySQL：在聚合函数中使用别名字段

I have a SQL statement similar to this:

我有一个类似于这样的SQL语句:

SELECT COUNT(*) AS foo, SUM(foo) AS foo_sum FROM bar

But MySQL doesn't allow this because foo is an alias. Does anyone have an idea of how this could be accomplished in SQL?

但MySQL不允许这样做,因为foo是别名。有没有人知道如何在SQL中实现这一点?

4 个解决方案

#1

No, you can't use an alias in the select-list or a WHERE clause. You can only use the alias in a GROUP BY, HAVING, or ORDER BY.

不,您不能在select-list或WHERE子句中使用别名。您只能在GROUP BY,HAVING或ORDER BY中使用别名。

You can also use aliases defined in a subquery:

您还可以使用子查询中定义的别名:

SELECT foo, SUM(foo) AS foo_sum
FROM (
  SELECT COUNT(*) AS foo
  FROM bar
);

#2

SELECT SUM(foo) as foo_sum
FROM 
(
    SELECT COUNT(*) AS foo
    FROM bar
    GROUP BY baz
)

#3

I think it's not a good idea. If you want to make a big query, it's better to do it without a subquery. Use COUNT(*) and bigger functions without alias, if you need it.

我认为这不是一个好主意。如果你想做一个大的查询,最好不要使用子查询。如果需要,可以使用COUNT(*)和不带别名的更大函数。

I made a query with aliases and subqueries. It took about an hour! Then I reproduced the query without the alias. It went down to about 45 minutes. Forget about subqueries in this situation. It's less hard and more pretty, but it makes your query slower.

我用别名和子查询进行了查询。花了大约一个小时!然后我重现了没有别名的查询。它下降到大约45分钟。在这种情况下忘记子查询。它不那么难,更漂亮,但它会让你的查询变慢。

#4

I don't know what you are trying to do but with above solutions you are running subquery on alias table which is not efficient.

我不知道你要做什么,但是使用上面的解决方案,你在别名表上运行子查询,效率不高。

SELECT foo 
FROM (SELECT COUNT(*) AS foo FROM employees) AS T;

Basically T is your alias table and it returns foo column with count which is single record and there is no meaning of using SUM(foo) function on it since it is single record.

基本上T是你的别名表,它返回foo列,其中count是单个记录,因为它是单个记录,所以没有使用SUM(foo)函数的意思。

Anyways simple answer:

无论如何简单的回答:

SELECT Count(1) AS foo from employees;

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the parentheses), you can use COUNT(1) to get better performance. Now the database engine will not have to fetch any data fields, instead it will just retrieve the integer value of 1.

由于COUNT函数将返回相同的结果,无论您包含哪些NOT NULL字段作为COUNT函数参数(即:在括号内),您可以使用COUNT(1)来获得更好的性能。现在,数据库引擎不必获取任何数据字段,而只是检索整数值1。

#1