SQL having，group by 与order by 的几点注意

当有聚合函数时（聚合函数为avg， sum等函数）可以用group by 与having

例子

SELECT  c.CompanyName,  COUNT(o.OrderID) AS account

FROM Northwind.dbo.Customers AS c, Northwind.dbo.Orders AS o, Northwind.dbo.[Order Details] AS od

WHERE c.CustomerID=o.CustomerID AND o.OrderID=od.OrderID

GROUP BY c.CompanyName HAVING COUNT(od.orderID)<=15

order by COUNT(o.OrderID) DESC;

 

如果没有聚合函数，可直接把限定条件放进where中进行限定，否则会提示错误像这样

olumn 'Northwind.dbo.Orders.ShippedDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

 

错误的范例

select o.OrderID, o.OrderDate, o.RequiredDate,o.ShippedDate, c.CompanyName, e.LastName+','+e.FirstName
FROM Northwind.dbo.Customers AS c, Northwind.dbo.Orders AS o, Northwind.dbo.Employees AS e
where c.CustomerID=o.CustomerID AND o.EmployeeID= e.EmployeeID  
GROUP BY c.CompanyName HAVING o.ShippedDate IS NOT null;

 

正确的范例

SELECT o.OrderID,

       o.OrderDate,

       o.RequiredDate,

       o.ShippedDate,

       s.CompanyName AS shippersCompanyName,

       c.CompanyName AS customersCompanyName,

       employees = e.LastName + ',' + e.FirstName

FROM   Northwind.dbo.Customers AS c,

       Northwind.dbo.Orders AS o,

       Northwind.dbo.Employees AS e,

       Northwind.dbo.shippers AS s

WHERE  c.CustomerID = o.CustomerID

       AND o.EmployeeID = e.EmployeeID

       AND o.ShipVia = s.ShipperID

       AND o.ShippedDate IS NOT NULL

ORDER  BY C.CompanyName; 

 

PS 前面没有聚合，这里 GROUP BY 需要前面的所有字段 HAVING

感谢论坛上的 wwwwgou和 fredrickhu