如何獲取表中每個用戶的最高分鐘日期？
我在嘗試解決這個問題時遇到了麻煩，有沒有人知道如何解決這個問題。
INSERT INTO watched_time (id,user_id, channel_id,minutes,`date`)
VALUES
(1,1,1,100.0,'2021-01-01 00:00:00.0'),
(2,1,1,180.0,'2021-01-02 00:00:00.0'),
(3,1,1,150.0,'2021-01-03 00:00:00.0'),
(4,1,1,110.0,'2021-01-04 00:00:00.0'),
(5,2,1,110.0,'2021-01-04 00:00:00.0'),
(6,2,1,140.0,'2021-01-05 00:00:00.0'),
(7,2,1,190.0,'2021-01-06 00:00:00.0'),
(8,3,1,170.0,'2021-01-01 00:00:00.0'),
(9,3,1,120.0,'2021-01-02 00:00:00.0'),
(10,3,1,130.0,'2021-01-03 00:00:00.0'),
(11,1,2,130.0,'2021-01-03 00:00:00.0'),
(12,2,2,130.0,'2021-01-03 00:00:00.0'),
(13,3,2,125.0,'2021-01-03 00:00:00.0'),
(14,1,2,110.0,'2021-01-05 00:00:00.0'),
(15,1,2,100.0,'2021-01-01 00:00:00.0'),
(16,2,2,120.0,'2021-01-01 00:00:00.0'),
(17,3,2,120.0,'2021-01-01 00:00:00.0');
uj5u.com熱心網友回復：
使用ROW_NUMBER：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY minutes DESC) rn
FROM yourTable
)
SELECT id, user_id, channel_id, minutes, date
FROM cte
WHERE rn = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/496279.html