創建刪除銷售額少於 10 的製造商的 DELETE 語句
訂單:
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
那個Customer
和SerialNumber
他們的那個,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
子句連結在一起來獲取所有製造商訂單的列表。讓我們只返回ID
of theManufacturer
,因為這就是我們現在關心的全部。請注意,由於這是所有製造商訂單的列表,因此每個 都會有一行,因此在他們有多個訂單的情況下,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
最後,我們想使用您提到的子查詢,因此我們可以
DELETE
從Manufacturer
表中獲取上述查詢的結果,如下所示: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
從上面的子查詢中刪除了該列。當然還有其他方法可以完成上述操作,但是為了將您需要理解的概念保持在最低限度,這是一個非常好的解決方案。我希望你從中帶走的概念是:
- Joins,特別是
INNER JOIN
Clause,你也應該研究Outer Joins。- 聚合函式如
COUNT()
,還有許多其他函式,如MIN()
、MAX()
和SUM()
等。- 該
GROUP BY
子句以及分組的一般工作方式。- 該
HAVING
條款以及如何以及何時使用它。