Mysql
在分組子句中選擇最大計數
我有以下表格：
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 中不受支持。如果有人能指出我的錯誤並且知道如何做到這一點，那將是很大的幫助！
這是一個很好的例子，說明了為什麼您應該定期升級伺服器 - 使用視窗函式（見下文）相對簡單 - 但是，也可以使用 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 的解決方案 - 無視窗函式（在此處進行操作）：
首先，我根據問題將表格放在一起：
CREATE TABLE vehicle ( vin VARCHAR (50) NOT NULL PRIMARY KEY, model VARCHAR (30) NOT NULL, category VARCHAR (30) NOT NULL );和：
CREATE TABLE sale ( sale_id INTEGER NOT NULL PRIMARY KEY -- nothing useful in sale - maybe rethink design? );和：
CREATE TABLE vehicle_sale ( vs_vin VARCHAR (50) NOT NULL, vs_sale_id INTEGER NOT NULL, vs_price INTEGER NOT NULL, CONSTRAINT vs_vin_fk FOREIGN KEY (vs_vin) REFERENCES vehicle (vin), CONSTRAINT vs_s_id_fk FOREIGN KEY (vs_sale_id) REFERENCES sale (sale_id) );並填充它們：
INSERT INTO vehicle VALUES ('v1', 'm1', 'car'), ('v2', 'm2', 'car'), ('v3', 'm2', 'car'), ('v4', 'm2', 'car'), ('v5', 'm5', 'elmo'), ('v6', 'm6', 'elmo'), ('v7', 'm6', 'elmo'), ('v8', 'm6', 'elmo'), ('v9', 'm9', 'truck'), ('v10', 'm10', 'truck'), ('v11', 'm10', 'truck'), ('v12', 'm10', 'truck'), ('v13', 'm10', 'truck'), ('v14', 'm10', 'truck');INSERT INTO sale VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (12), (13), (14);INSERT INTO vehicle_sale VALUES ('v1', 1, 100), -- m1 ('v2', 2, 200), -- m2 ('v3', 3, 300), -- m2 ('v4', 4, 400), -- m2 ('v5', 5, 500), -- m5 ('v6', 6, 600), -- m6 ('v7', 7, 700), -- m6 ('v9', 9, 800), -- m9 ('v12', 12, 900), -- m10 ('v13', 13, 1000), -- m10 ('v14', 14, 1100); -- m10所以，我做了一些探索性的分析查詢——只是為了展示這個過程——如果你有經驗的話可以跳過……
第一個 SQL：
SELECT v.vin, v.model, v.category, s.sale_id, vs.vs_vin, vs.vs_sale_id, vs_price FROM sale s INNER JOIN vehicle_sale vs ON s.sale_id = vs.vs_sale_id INNER JOIN vehicle v ON vs.vs_vin = v.vin;結果：
vin model category sale_id vs_vin vs_sale_id vs_price v1 m1 car 1 v1 1 100 v2 m2 car 2 v2 2 200 v3 m2 car 3 v3 3 300 v4 m2 car 4 v4 4 400 v5 m5 elmo 5 v5 5 500 v6 m6 elmo 6 v6 6 600 v7 m6 elmo 7 v7 7 700 v9 m9 truck 9 v9 9 800 v12 m10 truck 12 v12 12 900 v13 m10 truck 13 v13 13 1000 v14 m10 truck 14 v14 14 1100現在，我真的不明白為什麼你有一個vehicle_sale和一個sale表 - 它們之間存在完整的一對一對應關係。因此，其中之一是超出要求的。這也意味著 SQL 變得相當混亂，因為我們總是要通過sale表將****vehicle表連接到vehicle_sale表——需要更多的JOIN和更多的子查詢——但無論如何，仍然可以這樣做：
小提琴中還有幾個查詢 - 如果需要，您可以詢問它們，但我們的第一個要求是沒有模型的表（即查詢結果）。該型號的銷售如下：
SELECT v.category, v.model, COUNT(v.model) AS cnt FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sale_id = s.sale_id GROUP BY v.category, v.model ORDER BY v.category, cnt DESC;結果：
category model cnt car m2 3 car m1 1 elmo m6 2 elmo m5 1 truck m10 3 truck m9 1因此，我們看到在汽車類別中，我們有 3 輛 m2 車型和 1 輛 m1 車型的銷量。
但是，我們需要其中 n = 1 且組 = 類別/模型的最大 n 個組。所以，我們必須做到以下幾點：
SELECT category, MAX(cnt) AS mcnt FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sale_id = s.sale_id GROUP BY v.category, v.model ORDER BY v.category, cnt DESC ) AS a GROUP BY category ORDER BY category;結果：
category mcnt car 3 elmo 2 truck 3我們現在必須將此結果連接回包含模型的表（即上面的查詢結果）以實現我們的最終結果：
SELECT a.category, a.model, a.cnt, y.category, y.mcnt -- the y values are not required anymore! Left in for explanation FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sale_id = s.sale_id GROUP BY v.category, v.model ORDER BY v.category, cnt DESC ) AS a JOIN ( SELECT category, MAX(cnt) AS mcnt FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sale_id = s.sale_id GROUP BY v.category, v.model ORDER BY v.category, cnt DESC ) AS x GROUP BY category ORDER BY category ) AS y ON a.category = y.category AND a.cnt = y.mcnt ORDER BY a.category, a.cnt;結果：
category model cnt category mcnt car m2 3 car 3 elmo m6 2 elmo 2 truck m10 3 truck 3使用視窗函式的解決方案（在這裡小提琴）：
SELECT a.category, a.model, a.cnt FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt, ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sale_id = s.sale_id GROUP BY v.category, v.model ORDER BY v.category, cnt DESC ) AS a WHERE a.rn = 1 ORDER BY a.category;結果：我要去。