内部查询的最大值
为无人机配送公司创建一个数据库，通过无人机分发比萨饼。这家初创公司与几家无人机制造商和比萨饼店建立了合作关系。问题领域感兴趣的实体是:无人机制造商、无人机模型、无人机、比萨店、客户和送货。无人机制造商有一个名字，可以生产不同的无人机型号。无人机模型属于无人机制造商；它有一个名称、电池寿命(以分钟、整数为单位)和最大速度。真正的无人机是特定型号的，并有序列号。比萨饼店有名字和地址。顾客有自己的名字和忠诚度。比萨饼店的名字和顾客的名字是独一无二的。对于每次交付，系统记录相应的客户和比萨饼店，无人机的使用，以及日期和时间。我就是这样做的：
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；它拥有10架M1无人机、10架M2无人机和8架M3无人机。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“子句写得不正确。有人能帮我处理一下这个问题吗？
回答 1
Database Administration用户
发布于 2021-01-09 19:23:10
只是提醒您不能在聚合函数子句中使用WHERE (如MAX())，而这正是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(请注意，由于您厌倦了使用CTE，所以上面使用的是子查询，但是子查询中的逻辑可以同样地在CTE中创建，视图可以在这两种方式中都被等效地创建。)
DENSE_RANK()为您在ORDER BY子句中指定的逻辑排序的每一行生成一个ID。当有两行连接用于排序时(基于指定的逻辑)，则将为两者生成相同的ID。
在上面的查询中，我为每个制造商在无人机的分组COUNT()上(通过降序)订购，当两个制造商共享相同的无人机COUNT()时，它们得到相同的DroneCountRankId。在外部查询中，我在DroneCountRankId = 1上进行筛选，只给出拥有最大无人机数量的制造商(S)(如果在您的示例中是两个制造商，那么它们都是这样返回的)。(如果您将WHERE子句更改为DroneCountRankId = 2，那么所有制造商(S)都将拥有最多的2架无人机，等等)
我强烈建议通过上面提供的链接来阅读窗口函数，特别是DENSE_RANK()函数。
https://dba.stackexchange.com/questions/282907
复制相似问题
腾讯云开发者
