sql - Select with limit for the same id

Question

i have 3 tables

Posts_comments

comment_id, comment_post_id, comment_value, comment_time comment_user_id
1           1                test           DATETIME     1
2           1                test2          DATETIME     2
3           2                test3          DATETIME     2
4           1                test4          DATETIME     2
5           1                test5          DATETIME     1
6           1                test6          DATETIME     2

Members

member_id member_fistname member_lastname member_slug
1         John            Doe             john-doe
2         Test            User            test-user

Members_photos

member_user_id member_photo_type member_photo_name
1              2                 test.jpg
2              2                 test2.jpg

And i have sql

SELECT 
      posts_comments.comment_id, 
      posts_comments.comment_post_id, 
      posts_comments.comment_value, 
      posts_comments.comment_time, 
      members.member_id, 
      members.member_lastname, 
      members.member_fistname, 
      members_photos.member_photo_name 
FROM 
    posts_comments 
LEFT JOIN 
         members ON posts_comments.comment_user_id = members.member_id 
LEFT JOIN 
         members_photos ON members.member_id = members_photos.member_user_id 
AND 
   members_photos.member_photo_type = 2 
ORDER BY 
        posts_comments.comment_time DESC 
LIMIT 4

But this query show only last 4 comments independently from the comment_post_id. In this case i want to show last 4 comments for every comment_post_id (in this example: 4 comments where comment_post_id = 1 and 1 comment where comment_post_id = 2). I hope I wrote it clearly enough. Thx 4 help :)

标签： sqlmariadbgreatest-n-per-group

Answer 1

row_number()如果是 mariadb 版本MariaDB 10.2.0或更高版本，则使用窗口函数

select a.* from (    SELECT 
              posts_comments.comment_id, 
              posts_comments.comment_post_id, 
              posts_comments.comment_value, 
              posts_comments.comment_time, 
              members.member_id, 
              members.member_lastname, 
              members.member_fistname, 
              members_photos.member_photo_name,
             row_number()over(partition by  posts_comments.comment_post_id order by posts_comments.comment_time desc) rn
        FROM 
            posts_comments 
        LEFT JOIN 
                 members ON posts_comments.comment_user_id = members.member_id 
        LEFT JOIN 
                 members_photos ON members.member_id = members_photos.member_user_id 
        AND 
           members_photos.member_photo_type = 2
    ) a where a.rn<=4