【从Northwind学习数据库】汇总查询
前言
汇总查询最重要的函数大概就是GROUP BY了
这个函数的含义是按照后面的条件进行分组
然后再SELECT后面加上你想要查询的东西
利用以下几个函数
名称 | 函数 |
---|---|
求和 | SUM |
计数 | COUNT |
平均值 | AVG |
标准偏差 | STDEV |
就可以实现对数据的汇总
以下例题都在Northwind(北风)数据库的基础上进行操作,该数据库资源很多,请自行下载
例题
1.求每位员工的每个月份的业绩
分析题意
按照员工分组是第一个条件,其次是按照月份来分组,这时我们就要考虑到,如果只按月分组就会出现不同年份的某个月份被分到了一个组里,这种情况并不是我们想看到的,所以我们应该按照年月来分组 (划重点:按照年月来分组,不要只按照日期分组)
解题如下
--选择我们想要输出的姓名、年月、以及业绩
SELECT FirstName+' '+LastName 姓名,YEAR(OrderDate) 年份,
MONTH(OrderDate) 月份,SUM(UnitPrice*Quantity*(1-Discount)) 月业绩
--连接需要的表格
FROM Employees e
JOIN Orders o ON e.EmployeeID=o.EmployeeID
JOIN [Order Details] od ON o.OrderID = od.OrderID
--按照姓名和年月进行分组
GROUP BY LastName,FirstName,YEAR(OrderDate),MONTH(OrderDate)
2.求最近一个月销量排行前十的产品
分析题意
按照产品分组是第一个条件,最近一个月如何理解呢,是最近有订单的那个月 (不看日期,只看月份!!!),本题需要用到排名和取前n名的操作,这时候我们就能需要这两个函数了
名称 | 函数 |
---|---|
排序 | ORDER BY |
取前n名 | TOP |
解题如下
--选择我们想要输出的产品名和销量(此处用的是 TOP n WITH TIES ,并列第n名的会一同显示)
SELECT TOP 10 WITH TIES ProductName,SUM(Quantity) 销量
--连接需要的表
FROM Products p
JOIN [Order Details] od ON p.ProductID = od.ProductID
JOIN Orders o ON od.OrderID = o.OrderID
--筛选条件(年份=最近有订单的年份,月份=最近有订单的月份)
--此处用到了嵌套查询
WHERE MONTH(OrderDate) = (SELECT TOP 1 MONTH(OrderDate)
FROM Orders
ORDER BY OrderDate DESC)
AND YEAR(OrderDate) = (SELECT TOP 1 YEAR(OrderDate)
FROM Orders
ORDER BY OrderDate DESC)
--按照产品名称进行分组
GROUP BY ProductName
--按照总销量进行排序
--ORDER BY 为升序
--ORDER BY ~~ DESC为降序
ORDER BY SUM(Quantity) DESC
3.求按照月来观察销量波动最小的产品
分析题意
首先我们要先对产品按照产品名、年、月进行分组,对销量进行汇总,得到每月产品的总销量,接着对上述操作后得到的表格进行再次分组,按照产品名进行分组,对销量进行求标准方差,之后再进行排序取前操作。
解题如下
--选择最终要输出的产品名和标准偏差
SELECT TOP 1 ProductName,STDEV(Q) AS std_Q
--此处要对上一步的结果进行查询,所以要对上一步查询的结果命名
FROM(
--选择中间表格所需的列
SELECT ProductName,YEAR(OrderDate) AS Y,MONTH(OrderDate) AS M,
SUM([Order Details].Quantity) AS Q
--连接表格
FROM Products
JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID
JOIN Orders ON Orders.OrderID=[Order Details].OrderID
--按照产品名、年、月分组
GROUP BY ProductName,YEAR(OrderDate),MONTH(OrderDate)
) AS A
--按照产品名分组
GROUP BY ProductName
--标准方差升序
ORDER BY std_Q
4.求出所有有订单的员工都销售过的产品
分析题意
首先数出有订单的员工有多少人,然后数产品被多少个员工销售过,如果两者相等,那么就是满足条件的产品。
解题如下
--选择想要输出的产品名称和销售人数,使用DISTINCT函数进行去重操作
SELECT ProductName,COUNT(DISTINCT o.EmployeeID) 人数
--连接所需的表
FROM Orders o
JOIN Employees e ON O.EmployeeID=e.EmployeeID
JOIN [Order Details] od ON o.OrderID=od.OrderID
JOIN Products p ON od.ProductID=p.ProductID
--按照产品名来分组
GROUP BY ProductName
--注意,GROUP BY 后的筛选不能用WHERE,而应该用HAVING
HAVING COUNT(DISTINCT o.EmployeeID)=(SELECT COUNT(DISTINCT O.EmployeeID)
FROM Orders o
--此处我使用订单表左连接员工表,得到所有有订单的员工
LEFT JOIN Employees e ON O.EmployeeID=e.EmployeeID)
结尾
一家之言
如有疏漏,还请指出,希望一同进步