sql – 了解WHERE如何使用GROUP BY和Aggregation

select cu.CustomerID,cu.FirstName,cu.LastName,COUNT(si.InvoiceID)as inv --1
from Customer as cu inner join SalesInvoice as si --2
on cu.CustomerID = si.CustomerID -- 3
-- put the WHERE clause here ! --4   
group by cu.CustomerID,cu.LastName -- 5
where cu.FirstName = 'mark' -- 6

输出正确的代码 –

我得到错误 – 关键字’where’附近的语法不正确.

你能告诉我为什么会收到这个错误吗？我想知道为什么WHERE出现在GROUP BY之前而不是之后.

解决方法

select cu.CustomerID,COUNT(si.InvoiceID)as inv 
from Customer as cu 
inner join SalesInvoice as si 
   on cu.CustomerID = si.CustomerID 
where cu.FirstName = 'mark' 
group by cu.CustomerID,cu.LastName

如果要在GROUP BY之后执行过滤器,则将使用HAVING子句：

select cu.CustomerID,COUNT(si.InvoiceID)as inv 
from Customer as cu 
inner join SalesInvoice as si 
   on cu.CustomerID = si.CustomerID 
group by cu.CustomerID,cu.LastName
having cu.FirstName = 'mark'

HAVING子句通常用于聚合函数过滤,因此在GROUP BY之后应用它是有意义的

要了解这里的操作顺序是article explaining the order.从文章中SQL的操作顺序是：

To start out,I thought it would be good to look up the order in which SQL directives get executed as this will change the way I can optimize:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

使用此顺序,您将在GROUP BY之前的WHERE中应用过滤器. WHERE用于限制记录数.

可以这样想,如果你之后应用WHERE,那么你会返回更多的记录然后你想要分组.首先应用它,减少记录集然后应用分组.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!