如何在MySQL WHERE子句中使用别名列？

I have a MySQL query like this:

我有这样的MySQL查询:

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10;

I want to restrict the results to rows where Occurrences>0. This seems very simple to me, but I can't seem to make it work. No matter what I try, WHEREs or HAVINGs, whenever I try to add this restriction I get NO ROWS in return. I'm positive that there is data in my table that should be returned. Does anyone know how to accomplish what I'm doing?

我想将结果限制为Occurrences> 0的行。这对我来说似乎很简单,但我似乎无法使其发挥作用。无论我尝试什么,WHERE或HAVINGs,每当我尝试添加这个限制时,我都会得到NO ROWS作为回报。我很肯定我的表中有数据应该返回。有谁知道如何完成我正在做的事情?

I've tried this, but it still doesn't work. Any ideas why it still won't work?

我试过这个,但它仍然无效。任何想法为什么它仍然不起作用?

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" HAVING SUM(...some SQL removed for brevety)>0 GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10;

5 个解决方案

#1

I am not as familiar with MySQL as I am with SQL Server, but in T-SQL, you can't use aliases in GROUP BY clauses (I originally thought ORDER BY clauses as well, but that has since been shown to be incorrect). In this case, you want to filter based on the results of a GROUP BY, so I would use a HAVING clause as follows:

我不像SQL Server那样熟悉MySQL,但在T-SQL中,你不能在GROUP BY子句中使用别名(我最初也认为ORDER BY子句,但后来证明这是不正确的) 。在这种情况下,您希望根据GROUP BY的结果进行过滤,因此我将使用HAVING子句,如下所示:

SELECT *, SUM(... some SQL removed for brevity ...) AS Occurrences
FROM
    some_table AS q
WHERE
    criterion = 'value'
GROUP BY
    q.P_id
HAVING
    SUM(... some SQL removed for brevity ...) > 0
ORDER BY
    Occurrences DESC
LIMIT 10;

#2

This query works for me...

这个查询对我有用......

select order_id, sum(qty_ordered) as total_qty 
from sales_flat_order_item 
group by order_id 
having total_qty > 10
order by total_qty desc

#3

Ah, I've found the place for it.

啊,我找到了适合它的地方。

The statement is now:

声明现在是:

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" GROUP BY q.P_id HAVING SUM(...some SQL removed for brevety)>0 ORDER BY Occurrences DESC LIMIT 10;

It seems to be working fine with ORDER BY Occurrences,

ORDER BY Occurrences似乎工作得很好,

#4

I believe aggregates should be in the HAVING clause. Though, admittedly, I'm not sure if aliases are accepted.

我认为聚合应该在HAVING子句中。虽然,诚然,我不确定别名是否被接受。

HAVING (Occurrences > 0)

HAVING (SUM(...some SQL removed for brevety) > 0)

Alternatively, you can use a sub-query:

或者,您可以使用子查询:

SELECT *
FROM (
   -- your current query
) AS sub
WHERE (Occurences > 0)

#5

According to the SQL standard, the column aliases are not available in the body of the query, which is a confounded nuisance. Given what you have tried, it sounds as though the same is true of MySQL (and some other DBMS). You'll probably have to repeat the expression in the ORDER BY clause.

根据SQL标准,列别名在查询正文中不可用,这是一个混乱的麻烦。鉴于您的尝试,听起来好像MySQL(以及其他一些DBMS)也是如此。您可能必须在ORDER BY子句中重复该表达式。

Also, the GROUP BY clause should list every column that is in the *, not just the primary key (though, logically, specifying the primary key should be sufficient).

此外,GROUP BY子句应列出*中的每个列,而不仅仅是主键(但从逻辑上讲,指定主键应该足够)。

#1

SELECT *, SUM(... some SQL removed for brevity ...) AS Occurrences
FROM
    some_table AS q
WHERE
    criterion = 'value'
GROUP BY
    q.P_id
HAVING
    SUM(... some SQL removed for brevity ...) > 0
ORDER BY
    Occurrences DESC
LIMIT 10;

#2