동일한 ID에 대한 제한이 있는 선택

동일한 ID에 대한 제한이 있는 선택

나는 3개의 테이블을 가지고 있습니다.

게시물_댓글

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

회원들

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

회원_사진

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

그리고 저는 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

그러나 이 쿼리는 comment_post_id와 독립적으로 마지막 4개의 주석만 표시합니다.이 경우 모든 comment_post_id에 대해 마지막 4개의 댓글을 표시하고 싶습니다(이 예에서는 comment_post_id = 1인 4개의 댓글과 comment_post_id = 2인 1개의 댓글).제가 충분히 분명하게 썼기를 바랍니다.4개의 도움말 :)

사용하다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

언급URL : https://stackoverflow.com/questions/55828281/select-with-limit-for-the-same-id