多表联查

[lier_liujinming]

有一个book表主键book_id,包含外键user_id（作者名）

有一个chapter表，主键chapter_id,外键book_id,字段chapter_time（章节发布时间）

要列出已知user_id的一个用户的所有作品的最新章节，sql语句应该怎么写呢。

[RINK_1]

select * 
from book表 A
join chapter表 B ON A.book_id=B.book_id
where A.user_id='XXXXXX'
and not exists (select 1 from chapter表 where book_id=B.book_id and chapter_time>B.chapter_time)

[二月十六]

SELECT *
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY book.book_id ORDER BY chapter_time DESC) rn
    FROM chapter
        JOIN book
            ON chapter.book_id = book.book_id
    WHERE user_id = '用户id'
) t
WHERE rn = 1;

[lier_liujinming]

引用 1 楼 RINK_1 的回复:

select * 
from book表 A
join chapter表 B ON A.book_id=B.book_id
where A.user_id='XXXXXX'
and not exists (select 1 from chapter表 where book_id=B.book_id and chapter_time>B.chapter_time)

快吃饭了，晚上回来自己测试一下再给分

[lier_liujinming]

引用 2 楼 sinat_28984567 的回复:

SELECT *
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY book.book_id ORDER BY chapter_time DESC) rn
    FROM chapter
        JOIN book
            ON chapter.book_id = book.book_id
    WHERE user_id = '用户id'
) t
WHERE rn = 1;

快吃饭了，晚上回来测试一下再给分