SQL having,group by 与order by 的几点注意
来源:互联网 发布:mac怎么打开bootcamp 编辑:程序博客网 时间:2024/05/02 23:52
当有聚合函数时(聚合函数为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
- SQL having,group by 与order by 的几点注意
- sql where group by having order by
- SQL中order by 、group by 、having的用法区别
- SQL 中order by 、group by 、having的用法区别
- SQL中order by 、group by 、having的用法区别
- order by、group by、having的区别
- group by,having,order by的用法
- group by , order by , having 的用法
- group by 、 having 、 order by
- group by 、 having 、 order by
- having , group by , order by
- sql group by 与 having的用法
- sql group by 的having 与 where
- 关于group by 、group by having、where group by与 group by order by
- SQL 语句:order by 和group by 语句的注意点:
- SQL语句Group By和Having需要注意的地方
- sql中Distinct、Group by、having、order by使用注意事项
- sql中Distinct、Group by、having、order by使用注意事项
- EXT 组件一些属性与方法(panel)
- EXT 组件一些属性与方法(Tabpanel)
- HTML5笔记(1) - HTML5的定义
- eclipse插件安装的方法
- 设计模式-组合模式(Component)
- SQL having,group by 与order by 的几点注意
- pku 2104 -- K-th Number (sort +小优化水过)
- 死锁
- 【树】树算法之 B 树
- Effective Java读书笔记二
- Wiz 为知个人知识管理
- EXT 组件一些属性与方法(Tabpanel)
- 网格模型中级五
- 设计模式-模板方法模式(Template Method Pattern)