从表中为每个用户获取最后5行

Question

我需要你帮我解决这个问题。

场景：

用户可以投票任何数量的歌曲，但我只想选择最后5票每用户。我不想选最后5票。

表结构：

​

​

示例：

3个用户

• 卡尔，投了10首歌
• 罗伯特，投了6首歌
• 乔安妮，选了三首歌

我想得到卡尔最后5票，罗伯茨最后5票和乔尼斯3票。

当前的SQL查询：

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
FROM T_Songs AS songs
RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id
WHERE votes.P_Vote_id IN (
    SELECT P_Vote_id
    FROM T_Votes
    GROUP BY F_User_id
    HAVING P_Vote_id > MAX(P_Vote_id)-5

);

但是这个查询没有返回正确的选票计数。

解决方案(感谢戈登·林诺夫和保罗·斯皮格尔)

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
FROM T_Songs AS songs
RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id
WHERE users.nobility_house IS NOT NULL
AND votes.P_Vote_id >= coalesce(
(select votes2.P_Vote_id
from T_Votes votes2
where votes2.F_User_id = votes.F_User_id
order by votes2.P_Vote_id desc
limit 1 offset 4
), 0)
GROUP BY votes.F_User_id

Answer 1

假设“最后5”意味着ids最高的五个，您可以这样做：

select v.*
from t_votes v
where v.p_vote_id >= (select v2.p_vote_id
                      from t_votes v2
                      where v2.f_user_id = v.f_user_id
                      order by v2.p_vote_id desc
                      limit 1 offset 4
                     );

我会让你知道如何从其他表中引入列。

编辑：

如果少于5行：

select v.*
from t_votes v
where v.p_vote_id >= coalesce( (select v2.p_vote_id
                                from t_votes v2
                                where v2.f_user_id = v.f_user_id
                                order by v2.p_vote_id desc
                                limit 1 offset 4
                               ), p_vote_id
                             );

Answer 2

您可以在同一表上使用联接进行筛选：

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
    FROM T_Songs AS songs
    RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
    LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id        
LEFT JOIN (
    SELECT F_User_id, Max(P_Vote_id)-5 as MaxVoteId
    FROM T_Votes
    GROUP BY F_User_id
    ) DF ON ((DF.F_User_id = votes.F_User_id) AND (P_Vote_id >= MaxVoteId));