通过一对多关系表仅选择具有最大字段的一行 [英] Select only one row with max field with One-Many Relationship Table

问题描述

我有2个表，Chapter表有很多行，其中manga_id是Manga表的ID

I have 2 Table, the Chapter Table have many row with manga_id is id of Manga Table

漫画桌

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

And I looking for select only 1 manga have 1 chapter with order by 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

我真的很感谢任何人回答这个问题，并挽救我的生活

I really appreciate anyone answer in this topic and save my day

推荐答案

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 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

这应该可以解决问题.

这篇关于通过一对多关系表仅选择具有最大字段的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！