【從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)
結尾
一家之言
如有疏漏，還請指出，希望一同進步
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/277109.html
標籤：其他
上一篇：SQL陳述句創建視圖
下一篇：怎么保證介面的冪等性