內部查詢的最大值

為通過無人機配送披薩的無人機配送初創公司創建數據庫。這家初創公司與多家無人機製造商和比薩店建立了合作夥伴關係。問題域感興趣的實體是：無人機製造商、無人機模型、無人機、比薩店、客戶和傳遞。無人機製造商有名字，可以生產不同的無人機型號。無人機型號屬於無人機製造商；它有名稱、電池壽命（以分鐘為單位，整數）和最大速度。實際的無人機具有特定型號並具有序列號。比薩店有名稱和地址。客戶有姓名和忠誠度分數。比薩店名稱和客戶名稱是唯一的。對於每次傳遞，系統都會記錄相應的客戶和披薩店、使用的無人機以及日期和時間。我就是這樣進行的：

CREATE TABLE DroneManufacturers(
   id INT PRIMARY KEY IDENTITY(1,1),
   name VARCHAR(30)
);

CREATE TABLE DroneModels(
   id INT PRIMARY KEY IDENTITY(1,1),
   manufacturer_id INT REFERENCES DroneManufacturers(id),
   name VARCHAR(30),
   battery_life INT,
   max_speed INT
);

CREATE TABLE Drones(
   serial_number VARCHAR(10) PRIMARY KEY,
   model_id INT REFERENCES DroneModels(id),
);

CREATE TABLE PizzaShops(
   name VARCHAR(30) PRIMARY KEY,
   address VARCHAR(50)
);

CREATE TABLE Customers(
   name VARCHAR(30) PRIMARY KEY,
   score INT
);

CREATE TABLE Deliveries(
   pizza_shop VARCHAR(30) REFERENCES PizzaShops(name),
   customer VARCHAR(30) REFERENCES Customers(name),
   drone VARCHAR(10) REFERENCES Drones(serial_number),
   delivery_time DATETIME
);

我認為設計還可以，但是在下一個查詢中我遇到了一些問題：

創建一個視圖，顯示初創公司最喜歡的無人機製造商的名稱，即初創公司使用的無人機數量最多的製造商。範例：假設創業公司與 3 個製造商有合作關係：M1、M2、M3；它擁有M1的10架無人機，M2的10架無人機和M3的8架無人機。M1和M2是無人機數量最多的廠商。

我試過這樣：

SELECT t.name
FROM (
   SELECT dman.id, dman.name, COUNT(*) AS counts
   FROM Drones d
   INNER JOIN DroneModels dm
       ON d.model_id = dm.id
   INNER JOIN DroneManufacturers dman
       ON dm.id = dman.id
   GROUP BY dman.id, dman.name
   ) t
WHERE t.counts = MAX(t.counts)

我得到一個錯誤，因為最後一個“where”子句沒有正確編寫。有人可以幫我解決這個問題嗎？

只是提醒您不能在子句中使用聚合函式（如MAX()）WHERE，這就是該子句的目的HAVING。但不是使用聚合函式，而是像這樣的視窗函式DENSE_RANK()簡化了您正在尋找的解決方案，如下所示：

CREATE VIEW FavoriteDroneManufacturers AS

SELECT s.[name]
FROM
(
   SELECT dman.[name], DENSE_RANK() OVER (ORDER BY COUNT(1) DESC) AS DroneCountRankId
   FROM Drones d
   INNER JOIN DroneModels dm
       ON d.model_id = dm.id
   INNER JOIN DroneManufacturers dman
       ON dm.id = dman.id
   GROUP BY dman.id, dman.[name]
) AS s
WHERE s.DroneCountRankId = 1

（請注意，上面使用子查詢，因為您厭倦了使用CTEs，但是子查詢中的邏輯可以同樣在CTE中創建，並且可以在兩種方式上等效地創建視圖。）

DENSE_RANK()``ORDER BY為按您在子句中指定的邏輯排序的每一行生成一個 ID 。當有兩行並列排序時（基於指定的邏輯），將為兩者生成相同的 ID。

在上面的查詢中，我COUNT()對每個製造商的無人機分組進行排序（按降序排列），當兩個製造商共享相同COUNT()的無人機時，它們會得到相同的 DroneCountRankId。在外部查詢中，我過濾DroneCountRankId = 1以僅向我們提供無人機數量最多的製造商（如果在您的範例中是兩個製造商，那麼它們都以這種方式返回）。（如果您將WHERE條款更改為，DroneCountRankId = 2那麼您將獲得擁有前 2 名無人機數量的所有製造商等）

我強烈建議通過上面提供的連結閱讀視窗函式，DENSE_RANK()尤其是函式。

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