我有以下表格:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)
当我使用以下方法加入这些表时:
select YEAR(Sales.saleDate)
, Vehicles.model
, count(Vehicles.model) 'Sold'
, Vehicles.category
from Vehicles
JOIN vehicleSold
on Vehicles.vin = vehicleSold.vin
JOIN Sales
on Sales.saleID = vehicleSold.saleID
group
by YEAR(Sales.saleDate)
, Vehicles.model
, Vehicles.category;
结果是:
+----------------------+-------------+------+----------------+
| YEAR(Sales.saleDate) | model | Sold | category |
+----------------------+-------------+------+----------------+
| 2020 | Altima | 1 | car |
| 2020 | Flying Spur | 2 | car |
| 2020 | Lifan E3 | 3 | Electric Moped |
| 2020 | Ridgeline | 2 | truck |
| 2020 | Shiver | 4 | motorbike |
+----------------------+-------------+------+----------------+
从这张表中,我想得到一个类别中销量最高的型号。所以,在这种情况下,我只想退回一辆 2020 年的 Flying Spur,作为该类别汽车中唯一的一排,因为它是 2020 年同类产品中销量最高的。我尝试使用子查询是 MAX(COUNT(*)) 但我猜这在 mysql 中不受支持。如果有人能指出我的错误并且知道如何做到这一点,那将是很大的帮助!