【从Northwind学习数据库】汇总查询

【从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)

结尾

一家之言
如有疏漏，还请指出，希望一同进步