mysql - 通用化MYSQL查询所有user_id

Question

我有一个 mysql 查询

select sum(duration),app_name,app_id,user_id 
from timesheet 
where user_id=164 
group by app_id 
order by sum(duration) desc 
limit 5;

结果是

sum(duration), app_name,       app_id, user_id
'626919371',   'Idle.exe',     '0',    '164'
'38220511',    'LockApp.exe', '2204',  '164'
'36675000',    '',            '1',     '164'
'27713000',    'LockApp.exe', '8148',  '164'
'16698661',    'chrome.exe',  '8548',  '164'

但是，我想要每个 user_id 的前 5 个应用程序，而不仅仅是 164。您能否指导我如何在单个查询中实现这一点。或者我是否需要为每个单独的用户触发它

Answer 1

这应该有效:

SELECT * 
FROM (
    SELECT *, rank() over (PARTITION BY user_id ORDER BY duration desc) AS rank  
    FROM (
        SELECT user_id, app_id, sum(duration) AS duration 
        FROM timesheet
        GROUP BY user_id, app_id
    ) sub2
) sub 
WHERE rank < 6

如果您希望查询始终为每个用户返回 5 行(即使有一些具有相同排名的顶级应用程序)，请将 rank() 替换为 row_number()