我的查询 –
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之前而不是之后.
解决方法
你订单错了. 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,那么你会返回更多的记录然后你想要分组.首先应用它,减少记录集然后应用分组.