主页 / coding / 问题 / 79471567
Accepted
Phong Thai
Asked: 2025-02-27 11:57:36 +0800 CST

MySQL Select MAX() 和 Group By 无法按我想要的方式工作[重复]

  • 772

我有 2 张表，分别有 50k+ 和 10k+ 行

桌子chapter

id (auto_inc) | chapter_id | story_id | date (timestamp)
1             | 1          | 1        | 1715830560
...
10            | 10         | 1        | 1715830570 <- latest chapter of story #1

100           | 1          | 2        | 1715830560
...
111           | 11         | 2        | 1715830570 <- latest chapter of story #2

200           | 1          | 3        | 1715830560
...
211           | 21         | 3        | 1715830570 <- latest chapter of story #3

桌子story

id (auto_inc) | slug        | title     | date (timestamp)
1             | slug-1      | title 1   | 1715830560
...
100           | slug-100    | tit 100   | 1715830580 <- consider this is latest story

我使用下面的命令来获取每个故事的最新独特章节

SELECT C.id, MAX(C.chapter_id), C.story_id, C.title AS title_chapter, C.summary
FROM `chapter` C
GROUP by C.story_id
ORDER by C.id DESC
LiMIT 10;

它成功显示了 10 个独特的故事及其章节，但它总是按照最新故事来选择章节，这意味着它总是先选择最新的故事，然后再为每个故事选择最新的章节；结果如下所示：

id            | chapter_id | story_id (↓) | date (timestamp)
205           | 5          | 100          | 1715830560 <- 205 is latest chapter of story #100
108           | 8          | 99           | 1715830560 <- ..
8             | 8          | 98           | 1715830560 <- ..

但我想要按 id 排序的最新章节，也就是故事的最新章节，而不是带有章节的最新故事，结果如下所示：

id (↓)        | chapter_id (latest of each story) | story_id (unique) | date (timestamp)
508           | 15                                | 75                | 10
507           | 35                                | 95                | 9 
506           | 5                                 | 15                | 8 
505           | 15                                | 100               | 8 
504           | 35                                | 99                | 8

并且请向我建议解决方案（最佳性能和最短处理时间），以便从story表中获取这些最新章节的标题和 slug。

非常感谢您的建议。

mysql

1 个回答

  1. Best Answer

    您当前的查询无效，因为它按 进行聚合story_id，但随后选择非聚合列。您的代码甚至无法在ONLY_FULL_GROUP_BY启用该标志的 MySQL 上运行。解决您的问题的规范方法是使用ROW_NUMBER()窗口函数：

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY story_id ORDER BY chapter_id DESC) rn
    FROM chapter
)

SELECT id, chapter_id, story_id, title AS title_chapter, summary
FROM cte
WHERE rn = 1
ORDER BY date DESC
LIMIT 10;

    对于 MySQL 5.7 或更早版本：

    SELECT c1.id,  c1.chapter_id,  c1.story_id,  c1.title AS title_chapter,  c1.summary
FROM chapter c1
INNER JOIN (
    SELECT story_id, MAX(chapter_id) AS max_chapter_id
    FROM chapter
    GROUP BY story_id
) c2
    ON c2.story_id = c1.story_id AND
       c2.max_chapter_id = c1.chapter_id
ORDER BY c1.date DESC
LIMIT 10;
    • 4

相关问题