[MySQL / MariaDB] 取得每個group的前N筆資料
最近在工作上為了產報表,需要寫sql去撈每個group的top N筆紀錄。研究了一會兒後最後用了以下的解法。
假設今天有個payment
的資料表記錄了每筆交易所涉及的用戶、幣別、金額。
交易id(id) | 用戶id(uid) | 交易幣別(currency) | 金額(amount) |
---|---|---|---|
1 | 11 | NTD | 800 |
2 | 6 | NTD | 590 |
3 | 7 | NTD | 1020 |
4 | 55 | USD | 24 |
5 | 33 | USD | 27 |
6 | 2 | USD | 25 |
7 | 13 | JPY | 3870 |
8 | 10 | JPY | 2980 |
9 | 41 | JPY | 3780 |
我們想要取每個幣別的前2筆最大金額的交易記錄,我們可以這樣寫:
SELECT id, uid, currency, amount FROM ( SELECT id, uid, currency, amount, @counter := IF(@current_currency = currency, @counter + 1, 1) AS counter, @current_currency := currency AS current_currency FROM payment ORDER BY currency, amount DESC ) ordered_result WHERE counter <= 2;
近期迴響