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

本文介绍了在SQL中使用GROUP BY与HAVING进行数据分组和筛选的方法,并通过具体示例对比了正确与错误用法,帮助读者理解如何避免常见错误。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

当有聚合函数时(聚合函数为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

感谢论坛上的 wwwwgoufredrickhu

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值