Database-Design

創建刪除銷售額少於 10 的製造商的 DELETE 語句

  • February 27, 2021

訂單:

Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)

顧客:

ID (Primary Key)
Name
Phone Number
Street (ie. 505 Ramapo Valley Road)
City (ie. Mahwah)
State
Zip

機器:

Serial Number (Primary Key)
Model Number (Primary Key for Models table)

模型:

ID (Primary key)
Speed
RAM
HD
Price
Manufacturer ID (Primary key for Manufacturer table)

製造商:

ID (Primary Key)
Name
Phone Number
Email Address

以上是具有不同屬性的表。

我有一個即將到期的實驗室,其中一項任務是創建一個 SQLite 語句來刪除銷售額少於十個的製造商。我試圖編寫一個子查詢,但無法制定一個有效的語句。以下是我到目前為止所擁有的:(我知道我偏離了很多)

DELETE FROM Manufacturer    
HAVING COUNT(*) < 10
WHERE ID IN
(SELECT ManufacturerID FROM Model WHERE Manufacturer
   WHERE Manufacturer.ID = Model.ManufacturerID

SELECT ModelNo FROM Machines
   WHERE Machines.ModelNo = Model.ID

SELECT SerialNo FROM Orders
   ON Orders.SerialNo = Machines.SerialNo

所以你離得併不遠,你只是錯過了幾個關鍵的東西,第一個是JOIN關鍵字。JOIN是如何組合兩個相關數據集的方法。有不同類型的連接,其中一種INNER JOIN是您可以組合兩個數據集並僅返回與連接條件匹配的行。例如,如果您只想獲取Customers擁有 an 的人,Order並且您想知道Name那個CustomerSerialNumber他們的那個,Order您可以使用INNER JOIN這樣的方法:

SELECT Customers.Name, Orders.SerialNumber
FROM Customers
INNER JOIN Orders
   ON Customers.ID = Orders.CustomerID

您可以在單個查詢中加入任意數量的數據集。因此,如果我們還想知道ModelNumber上述客戶訂單的資訊,我們可以像這樣修改上述查詢:

SELECT Customers.Name, Orders.SerialNumber, Machines.ModelNumber
FROM Customers
INNER JOIN Orders
   ON Customers.ID = Orders.CustomerID
INNER JOIN Machines
   ON Orders.SerialNumber = Machines.SerialNumber

從上面的範例中,我們可以通過將一堆INNER JOIN子句連結在一起來獲取所有製造商訂單的列表。讓我們只返回IDof the Manufacturer,因為這就是我們現在關心的全部。請注意,由於這是所有製造商訂單的列表,因此每個 都會有一行,因此在他們有多個訂單的情況下,Order您應該會看到相同ID的重複:Manufacturer

SELECT Manufacturer.ID
FROM Manufacturer
INNER JOIN Model
   ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
   ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
   ON Machines.SerialNumber = Orders.SerialNumber

現在您需要一種方法來計算上一個查詢Orders中每個查詢的數量(多少行)Manufacturer.ID。為此,我們可以使用內置COUNT()函式。因為我們要計算Orders特定條件的數量(按Manufacturer),所以我們還需要使用GROUP BY子句。該GROUP BY子句允許我們按照我們指定的標準將所有行匯總(展平)為基於我們指定的列的每個分組的單個唯一行。在這種情況下,我們只需要一個單行,Manufacturer.ID這樣當我們使用該COUNT()函式時,它會計算該Orders分組中的數量,如下所示:

SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
   ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
   ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
   ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID

因此,上面的查詢現在為我們提供了製造商 ID 列表以及Orders每個製造商 ID 的數量。讓我們將其過濾到只有小於 10 的製造商Orders。我們將使用HAVING子句來完成此操作,這相當於使用WHERE子句過濾數據,但只能與聚合函式一起使用,如COUNT()分組數據:

SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
   ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
   ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
   ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID
HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders

最後,我們想使用您提到的子查詢,因此我們可以DELETEManufacturer表中獲取上述查詢的結果,如下所示:

DELETE FROM Manufacturer
WHERE ID IN
(
   SELECT Manufacturer.ID
   FROM Manufacturer
   INNER JOIN Model
       ON Manufacturer.ID = Model.ManufacturerID
   INNER JOIN Machines
       ON Model.ID = Machines.ModelNumber
   INNER JOIN Orders
       ON Machines.SerialNumber = Orders.SerialNumber
   GROUP BY Manufacturer.ID
   HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders
)

請注意,當您在IN關鍵字內部使用子查詢時,您只能返回一列,對於您的情況,您只關心需要刪除ID的對象。Manufacturers所以我OrderCount從上面的子查詢中刪除了該列。

當然還有其他方法可以完成上述操作,但是為了將您需要理解的概念保持在最低限度,這是一個非常好的解決方案。我希望你從中帶走的概念是:

  1. Joins,特別是INNER JOINClause,你也應該研究Outer Joins
  2. 聚合函式COUNT(),還有許多其他函式,如MIN()MAX()SUM()等。
  3. GROUP BY子句以及分組的一般工作方式。
  4. HAVING條款以及如何以及何時使用它。

引用自:https://dba.stackexchange.com/questions/286147