我有以下表格:
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 中不受支持。如果有人能指出我的错误并且知道如何做到这一点,那将是很大的帮助!
您可以使用窗口函数为每个值的分组 () 中的
ROW_NUMBER()
每个记录生成唯一 ID ,如下所示:PARTITION
category
(对于这类事情,我通常喜欢使用 CTE 而不是子查询,但我认为您的 MySQL 版本没有 CTE,所以上面使用了子查询。)
这是一个很好的例子,说明了为什么您应该定期升级服务器 - 使用窗口函数(见下文)相对简单 - 但是,也可以使用 MySQL 5.6 版,如下所示(下面的所有代码也可以在这个小提琴):
我使用“纯” SQL 进行了此操作 - 对于使用 MySQL 变量的解决方案,请参阅此处(通常是一个很好的查询站点),或者搜索“greatest-n-per-group MySQL 5.6”或类似术语。
只是为了说明使用窗口函数会变得多么容易,看看这里- 它将 SQL 从 37 行减少到 17 行 - 计划(我使用 MySQL 版本 8.0.23 - 从 8.0.16,你有解释ANALYZE)对于 5.6(无窗口函数)解决方案要复杂得多 - 使用分析的性能(8 次运行 - 更改顺序) - 5.6 版本需要 50% - 100% 的时间(有时更多......)!SQL 位于此答案的底部 - 请参阅此处的小提琴!
使用 SQL 的解决方案 - 无窗口函数(在此处进行操作):
首先,我根据问题将表格放在一起:
和:
和:
并填充它们:
所以,我做了一些探索性的分析查询——只是为了展示这个过程——如果你有经验的话可以跳过……
第一个 SQL:
结果:
现在,我真的不明白为什么你有一个vehicle_sale和一个sale表 - 它们之间存在完整的一对一对应关系。因此,其中之一是超出要求的。这也意味着 SQL 变得相当混乱,因为我们总是要通过sale表将vehicle表连接到vehicle_sale表——需要更多的JOIN和更多的子查询——但无论如何,仍然可以这样做:
小提琴中还有几个查询 - 如果需要,您可以询问它们,但我们的第一个要求是没有模型的表(即查询结果)。该型号的销售如下:
结果:
因此,我们看到在汽车类别中,我们有 3 辆 m2 车型和 1 辆 m1 车型的销量。
但是,我们需要其中 n = 1 且组 = 类别/模型的最大 n 个组。所以,我们必须做到以下几点:
结果:
我们现在必须将此结果连接回包含模型的表(即上面的查询结果)以实现我们的最终结果:
结果:
使用窗口函数的解决方案(在这里小提琴):
结果:同上。