我有一个非常简单的问题。我正在使用Mysql长凳,我有一个数据,如下所示:
dateordered_new orderstatus orders
2016-06-23 23:19:23 returned 8
2016-06-01 23:19:23 completed 12
2016-06-22 23:19:23 returned 9
2016-06-04 23:19:23 completed 27
...etc...
问题很简单,我想显示每个月退回的订单数量。
这是我的查询:
select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
group by month
having orderstatus='returned;
考虑到 where 子句和有子句之间的区别,我的语法应该工作。但是,系统告诉我"错误代码:1054。"有子句"中的未知列"订单状态",它是有线的。
但是,当我像这样修改我的查询时:
select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
where orderstatus='returned
group by month;
它奏效了。
所以,这真的很令人困惑。我认为有子句应该遵循分组声明。但我无法回答为什么会发生这个案子?
你们对此有什么想法吗?
在您的情况下,您应该使用 where
子句:
select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
where orderstatus='returned'
group by month
因为您希望在聚合行之前筛选表中的行。
仅当您要过滤聚合值时having
才使用子句,例如
select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
group by month
having sum(orders) > 10
但是,mysql 很灵活,允许您对非聚合值使用having
。
HAVING
用于过滤掉聚合的结果,如 MIN/MAX/AVERAGE
,而WHERE
用于过滤非聚合列。
例如,您可以执行以下操作:
select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
WHERE orderstatus='returned'
group by month
having sum(orders) < 100
您必须将 having 子句提到的条件替换为 where 子句。因为having clause filter the data on aggregated group
和where clause filter the data on whole record set
.
请尝试以下 SQL:
Select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
where orderstatus='returned
group by month;
"WHERE" 子句过滤单个行值...
where colname > 0
或
where colname = 'sometext'
'HAVING 子句过滤一行的组或聚合,如果存在,则位于"分组依据"语句之后......
group by colname
having count(*) > 0
或
group by colname
having sum(colname) < 1