单选"使用联接查询最大计数"

Question

我有三个表：客户、车辆和销售。我需要一个 SELECT 语句，该语句将形成包含三列的输出：VEHICLEMAKE、SALESBYMAKE 和 CUSTOMERFULLNAME。输出应类似于以下内容：

VEHICLEMAKE SALES_BY_MAKE CUSTOMERFULLNAME
Chevrolet              11 Adam E Whitney           
Alberto L Ross           
Alexis T Moon            
Finley H Tritt           
Jayda V Rush             
Junior E Hanes           
Kamari H Webster         
Linda L Lawrence         
Luke E Boyer             
Samantha D Holden        
Sydnee B Herman 

该列表应仅包含 SALESBYMAKE 的最大数量，除非 MAX 是绑定的，否则它还将包含 VEHICLEMAKE、SALESBYMAKE 编号和其他 VEHICLEMAKE 的客户全名。我已经工作了几个小时，但绝对无济于事！以下是仅包含必要属性的表。

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerFirName     VARCHAR(20) NOT NULL,
customerLasName     VARCHAR(20) NOT NULL,
customerMiName      VARCHAR(1)  NOT NULL;
CREATE TABLE VEHICLES
(vehicleVIN     VARCHAR(25) PRIMARY KEY,
vehicleMake     VARCHAR(15) NOT NULL;
CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
vehicleVIN      VARCHAR(25);
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID),
CONSTRAINT SALES_FK2 FOREIGN KEY (vehicleVIN) REFERENCES VEHICLES(vehicleVIN);

编辑：这是我尝试过的：

SELECT DISTINCT v.VEHICLEMAKE, COUNT(*) OVER (PARTITION BY s.VEHICLEVIN) "SALES_BY_MAKE", c.CUSTOMERFULLNAME, COUNT(*) OVER (PARTITION BY c.CUSTOMERZIP)"CUSTOMERS"
FROM SALES s, VEHICLES v, CUSTOMERS c
WHERE s. VEHICLEVIN = v. VEHICLEVIN
and c. CUSTOMERID = s. CUSTOMERID
ORDER BY 2 DESC;

问题是，这给了我整个列表，而不是 MAX 值。我不知道确切的位置或如何实现 MAX 语句，以仅包含购买最多的车辆品牌。

我也尝试过这个，正如我问的另一个人的建议，但无法理解它，它没有错误地执行。

SELECT vc.*
FROM (SELECT vc.*, MAX(SALES_BY_MAKE) OVER () as MAX_SALES_BY_MAKE
FROM (SELECT v.VEHICLEMAKE, c.CUSTOMERFULLNAME,
COUNT(*) OVER (PARTITION BY s.VEHICLEVIN) as SALES_BY_MAKE
FROM SALES s JOIN
VEHICLES v
ON s.VEHICLEVIN = v VEHICLEVIN JOIN
CUSTOMERS c
ON c.CUSTOMERID = s.CUSTOMERID 
) vc
) vc
WHERE SALES_BY_MAKE = MAX_SALES_BY_MAKE
ORDER BY VEHICLEMAKE;

我认为我尝试的第一个是正确的向下路径，因为它确实提供了我需要的列表，但是它不仅选择 MAX 值，而是提供所有值并从最高到最低排序。我考虑过添加一个限制，但这不允许"平局"。

我真的只需要关于在哪里/如何实现MAXcount以获得最高输出的指导。

Answer 1

尝试此查询，只是它将重复前 2 列的值

SELECT  C.vehicleMake,C.nCount,B.customerFirName + ' ' + B.customerMiName     + ' ' + B.customerLasName cFullName
FROM SALES A
JOIN CUSTOMERS B ON A.customerID = B.customerID
JOIN    (
SELECT A.vehicleVIN,B.vehicleMake,COUNT(*) nCount
FROM SALES
JOIN VEHICLES B ON A.vehicleVIN = B.vehicleVIN
GROUP BY A.vehicleVIN,B.vehicleMake
)C ON A.vehicleVIN = C.vehicleVIN

Answer 2

这最终起到了作用！

WITH
vehicleMakeCount AS
(
SELECT
SALES.saleID, SALES.customerID, SALES.vehicleVIN, VEHICLES.vehicleMake,
COUNT(*) OVER (PARTITION BY VEHICLES.vehicleMake) AS salesByMake
FROM
SALES
INNER JOIN
VEHICLES ON VEHICLES.vehicleVIN  = SALES.vehicleVIN
),
salesRank AS
(
SELECT
vehicleMakeCount.*,
RANK() OVER (ORDER BY salesByMake DESC)   AS vehicleMakeSalesRank
FROM
vehicleMakeCount
)
SELECT CUSTOMERS.customerFullName, salesRank.vehicleMake
FROM
salesRank
INNER JOIN
CUSTOMERS
ON CUSTOMERS.customerID = salesRank.customerID
WHERE
salesRank.vehicleMakeSalesRank = 1
ORDER BY salesRank.vehicleMake;