mysql - 请求一个sql查询

Question

我有一个表，例如 posts 具有这种结构和数据:

ID | CatID | SubCatID | Title
-----------------------------
1 | 84 | 85 | Test 1
2 | 84 | 86 | Test 2
3 | 84 | 87 | Test 3
4 | 84 | 85 | Test 4
5 | 84 | 85 | Test 5
6 | 84 | 86 | Test 6

我想要 1 个查询返回按 SubCatID 分组的行，并返回每个 SubCat 具有 MaxID 的行

这意味着我要返回这个列表:

ID | CatID | SubCatID | Title
--------------------------------
5 | 84 | 85 | Test 5
6 | 84 | 86 | Test 6
3 | 84 | 87 | Test 3

Answer 1

SELECT t.ID, t,CatID, t.SubCatID, t.Title
    FROM YourTable t
        INNER JOIN (SELECT MAX(ID) AS MaxID, CatId, SubCatID
                        FROM YourTable
                        GROUP BY CatId, SubCatID
                   ) q
            ON t.CatId = q.CatId
                AND t.SubCatID = q.SubCatID
                AND t.ID = q.MaxID