我有一個這樣的查詢：
select count(1) num, business_id, category_id
from mytable
group by business_id, category_id
order by num desc
結果如下所示：
// res
----- ------------- -------------
| num | business_id | category_id |
----- ------------- -------------
| 22 | 5543 | 8 |
| 19 | 4352 | 8 |
| 13 | 3242 | 11 |
| 10 | 2132 | 11 |
| 7 | 6832 | 8 |
----- ------------- -------------
現在我想為每個category_id. 所以它必須是最大的num和它的business_id。所以預期的結果是：
// expected res
----- ------------- -------------
| num | business_id | category_id |
----- ------------- -------------
| 22 | 5543 | 8 |
| 13 | 3242 | 11 |
----- ------------- -------------
我怎樣才能做到這一點？
uj5u.com熱心網友回復：
如果你的 MySQL 版本支持ROW_NUMBER 視窗函式，你可以嘗試使用ROW_NUMBER得到最大num的category_id
查詢#1
SELECT num,business_id,category_id
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY num desc) rn
FROM (
select count(1) num, business_id, category_id
from mytable
group by business_id, category_id
) t1
) t1
WHERE rn = 1
| 數量 | 業務編號 | 類別編號 |
|---|---|---|
| 22 | 5543 | 8 |
| 13 | 3242 | 11 |
在 DB Fiddle 上查看
uj5u.com熱心網友回復：
使用視窗函式MAX()獲取最大值num并FIRST_VALUE()獲取business_id最大值num：
SELECT DISTINCT
MAX(COUNT(*)) OVER (PARTITION BY category_id) num,
FIRST_VALUE(business_id) OVER (PARTITION BY category_id ORDER BY COUNT(*) DESC) business_id,
category_id
FROM mytable
GROUP BY business_id, category_id
ORDER BY num DESC;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/400244.html
標籤：mysql sql 通过...分组 sql-order-by 窗函数
上一篇：如何安全地連接到MySQL資料庫