Query

如何從一個表中獲取對應於另一個表的列

  • April 27, 2021

我有這些表：

訂單詳細資訊： 在此處輸入圖像描述

訂單： 在此處輸入圖像描述

僱員： 在此處輸入圖像描述

產品： 在此處輸入圖像描述

顧客： 在此處輸入圖像描述

我想寫一個查詢，我可以在其中獲取訂單數量最大的員工的姓氏。我設法編寫了以下查詢：

'Selects biggest purchase from OrderDetails'
SELECT MAX(Quantity) FROM [OrderDetails];

'Selects the OrderID of orders'
SELECT OrderID FROM [OrderDetails];

SELECT EmployeeID FROM [Orders]
SELECT OrderID FROM [Orders]

但我不知道如何將它們結合起來。

由於我不確定您是在尋找在單個訂單中售出最多商品的人，還是在一段時間內售出最多商品的人，所以這裡有一個粗略的查詢，允許你會看到：

SELECT e.LastName, tmp.MaxQuantity, tmp.SumQuantity
 FROM Employees e INNER JOIN (SELECT o.EmployeeID, MAX(od.Quantity) as MaxQuantity, SUM(od.Quantity) as SumQuantity
                                FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
                               GROUP BY o.EmployeeID) tmp ON e.EmployeeID = tmp.EmployeeID
ORDER BY tmp.MaxQuantity DESC;

如果您想知道誰在單個 OrderDetail 行上銷售了最多的產品，請使用ORDER BY tmp.MaxQuantity DESC. 如果您想知道誰售出了最多的產品，請使用ORDER BY tmp.SumQuantity DESC`。

請務必將此查詢編輯為：

  • 將行數限制在合理的範圍內
  • 將日期範圍限制在合理的範圍內（除非您確實需要 25 年的數據）
  • 考慮到那些Employees離開公司後可能不再在餐桌上的人

編輯以顯示可能的“按國家過濾”選項：

如果您對按國家或其他客戶屬性進行過濾感興趣，可以將Customers表添加到子查詢中，如下所示：

SELECT e.LastName, tmp.MaxQuantity, tmp.SumQuantity
 FROM Employees e INNER JOIN (SELECT o.EmployeeID, MAX(od.Quantity) as MaxQuantity, SUM(od.Quantity) as SumQuantity
                                FROM Customers cu INNER JOIN Orders o ON cu.CustomerID = o.CustomerID
                                                  INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
                               WHERE cu.CountryCode = 'DE'
                               GROUP BY o.EmployeeID) tmp ON e.EmployeeID = tmp.EmployeeID
ORDER BY tmp.MaxQuantity DESC;

**注意：**我不知道你的Customers表的結構，所以一定要相應地編輯它。

如果您的Customers表格使用完整的國家/地區名稱，您可能需要仔細檢查是否存在導致數據差異的“簡短表格”。一個很好的例子是“美利堅合眾國”，它可以寫成完整的，或者寫成“USA”或“USA”或“US”或“US”等等。根據您的數據庫的佈局方式，這可能是一個很好的機會來仔細檢查您希望過濾的列的數據是否一致。

最終編輯以顯示與原始問題完全不同的內容：

SELECT p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, p.Unit, COUNT(od.Quantity) as TotalOrdered
 FROM Categories c INNER JOIN Products p ON c.CategoryID = p.CategoryID
                   INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
                   INNER JOIN Order o ON od.OrderID = o.OrderID
                   INNER JOIN Customers cu ON o.CustomerID = cu.CustomerID
WHERE cu.Country = 'Germany' and o.OrderDate BETWEEN '1990-01-01' AND '2021-12-31'
GROUP BY p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, p.Unit
ORDER BY TotalOrdered DESC
LIMIT 25;

**注意：**此查詢適用於 MySQL、MariaDB 和 PostgreSQL。如果您使用 SQL Server、SQL Express、SQLite、Oracle 或其他一些數據庫引擎，則需要調整語法以匹配該系統。

要回答您最初的問題：

您應該首先從要獲取數據的對像開始，在這種情況下Employees：

SELECT LastName
FROM Employees -- Gets the LastName of every employee

現在我們需要一種Orders獲取資訊的方法，因為您想要“訂單量最大的員工”。我們將Order記錄與Employee記錄關聯的方式是使用JOIN運算符。在這種情況下，anINNER JOIN就足夠了，因為INNER JOIN僅在兩個表之間匹配時才返回行。（這將過濾掉Employees誰沒有Orders我們想要的，因為您正在尋找最大的訂單數量。）

SELECT Employees.LastName -- Specifying the Table name or using an Alias is good practice to avoid errors when the same column exists in two tables that are part of the same query (not the case here but it's best practice to do for readability too).
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
   ON Employees.EmployeeId = Orders.EmployeeId

現在我們需要做同樣的事情來引入數據OrderDetails所在的位置Quantity。anINNER JOIN在這裡應該是合適的，如下所示：

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
   ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
   ON Orders.OrderID = OrderDetails.OrderID

請注意我在查詢中的評論。由於查詢的第一個 INNER JOIN 為每個 Employee 的每個 Order 返回一行，第二個 INNER JOIN 為每個 Order 的每個 OrderDetail 返回一行，因此整個查詢現在為每個 Employee 的每個 Order 的每個 OrderDetail 返回一行，所以您的行數本質上將是Employees x Orders x OrderDetails。

現在我們已經有了上面查詢的Employee Order Quantities的所有行，我們希望按最大Quantity降序對數據進行排序，然後過濾掉除第一條記錄之外的所有內容。我們可以通過一個ORDER BY子句和一個LIMIT（TOP如果您使用的是 Microsoft SQL Server）來實現這一點，如下所示：

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
   ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
   ON Orders.OrderID = OrderDetails.OrderID
ORDER BY OrderDetails.Quantity DESC
LIMIT 1;

這裡的最後一點更多的是關於您的OrderDetails表格代表什麼的問題（正如其他一些使用者所暗示的那樣）？就像您的範例數據顯示的那樣， per中是否總是只有一行，或者可以與表中的多行相關？OrderDetails``Order``Order``OrderDetails

如果 anOrder可以與多個相關，OrderDetails那麼您需要再執行一個稱為 a 的操作GROUP BY，這將允許我們壓縮OrderDetail屬於同一Orderper的多行，Employee然後Quantities通過對它們求和來聚合它們，如下所示：

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
   ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
   ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName, Orders.OrderID -- This condense the rows back down to just one row per Order per Employee. 
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;

請注意，上面為您提供了單曲中最大LastName的人。例如，如果 EmployeeA 有一個 Quantity = 10 的 Order，而 EmployeeB 有兩個不同的 Order，一個 Quantity = 7，另一個 Quantity = 5，上面的查詢將得到 EmployeeA 的。但是，如果您想將所有不同的相同內容壓縮為一行進行比較，例如在我之前的範例中，您需要 EmployeeB（因為他的兩個訂單數量之和 = 12，這在 EmployeeA 的唯一訂單中大於 10 ) 然後您可以放鬆該子句以僅對欄位進行分組，如下所示：Employee``Order Quantity OrderLastName``Quantities``Orders``Employee``GROUP BY``Employee.LastName

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
   ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
   ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName -- This condense the rows down to just one row per Employee's LastName. 
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;

現在讓我們用上面學到的知識來回答您在“德國客戶訂購的產品最多？ ”的評論中的第二個問題：

-- Gets the Product with the highest Quantity on a single OrderDetail
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
   ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
   ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
   ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
ORDER BY OrderDetails.Quantity DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity

或者：

-- Gets the Product with the highest Quantity on a single Order
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
   ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
   ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
   ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName, Orders.OrderID -- Condenses all the OrderDetails rows to a single Order per Product and sums the Quantities for a total per Product per Order
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity

或者：

-- Gets the Product with the highest Quantity across all Orders
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
   ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
   ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
   ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName -- Condenses all the OrderDetails and Orders rows to a single row per Product and sums the Quantities for a total per Product
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity

引用自：https://dba.stackexchange.com/questions/290524

相關問答

Query-Performance

連接列值的更好方法

  • July 11, 2022
檢視問答
Postgresql

簡單 SQL 查詢中的語法錯誤

  • July 7, 2022
檢視問答
Mysql

MySQL用逗號分隔值連接兩個表

  • December 28, 2020
檢視問答
Sql-Server

如何在“IN”語句中使用關鍵字“VALUES”？

  • December 8, 2020
檢視問答
Query

SQL CASE 查詢優先級

  • July 15, 2020
檢視問答
Oracle-11g-R2

使用不返回所需結果集的“邏輯運算符 (AND)”進行查詢

  • July 4, 2020
檢視問答