我有 2 个表，Chapter表有很多行，manga_id 是 Manga 表 的 id
漫画表
ID | NAME | SLUG
1 One Piece one-piece
2 Naruto naruto
3 Gintama gintama
章节表
MANGA_ID | NAME | SLUG | CREATED_AT
1 Chap 1 One-Piece-Chap-1 2016/01/01
1 Chap 2 One-Piece-Chap-2 2016/01/02
1 Chap 3 One-Piece-Chap-3 2016/01/03
2 Chap 1 Naruto-Chap-1 2016/01/02
2 Chap 2 Naruto-Chap-2 2016/01/03
2 Chap 3 Naruto-Chap-3 2016/01/04
3 Chap 1 Gintama-Chap-1 2016/01/07
3 Chap 4 Gintama-Chap-4 2016/01/09
我正在寻找只选择 1 漫画有 1 章，按 created_at DESC 订购
我寻找的示例表：
Manga_ID | Manga_NAME | NAME | SLUG | CREATED_AT
3 Gintama Chap 4 Gintama-Chap-4 2016/01/09
2 Naruto Chap 3 Naruto-Chap-3 2016/01/04
1 One Piece Chap 3 One-Piece-Chap-3 2016/01/03
我非常感谢任何人回答这个话题并节省我的一天
SELECT
mt.id as MANGA_ID,
mt.NAME as MANGA_NAME,
ct.NAME,
ct.SLUG,
MAX(ct.CREATED_AT) as CREATED_AT
FROM
Chapter ct
LEFT JOIN
Manga mt
ON (mt.id = ct.MANGA_ID)
GROUP BY
ct.MANGA_ID
这应该可以解决问题。
SELECT t1.MANGA_ID, t2.NAME AS Manga_NAME,t1.NAME,t1.slug,t1.CREATED_AT
FROM Chapter t1
INNER JOIN Manga t2 ON t1.MANGA_ID = t2.ID
WHERE t1.CREATED_AT = (SELECT MAX(CREATED_AT) from Chapter t3 where t3.MANGA_ID = t1.MANGA_ID)