如何更新查询以符合已启用的SQL_MODE=only_full_group_by？[副本]

Question

在上次MySQL更新后，我一直在寻找一种方法来纠正我的查询。我知道Group By列不是唯一的，但我不知道如何更新查询以遵守严格的规则，并且仍然保留我要查找的结果。有人有什么建议吗？

SELECT 
    $SQLTable_Posts.`post_id`, 
    $SQLTable_Posts.`profile_id`, 
    $SQLTable_Posts.`post_title`, 
    $SQLTable_Posts.`post_slug`, 
    $SQLTable_Posts.`post_date`
FROM $SQLTable_Posts
WHERE $SQLTable_Posts.`post_date` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY $SQLTable_Posts.`profile_id`
ORDER BY $SQLTable_Posts.`post_date` DESC

我想简单地返回每个配置文件所做的最新帖子。例如，我的数据库看起来是这样的；

<table><tbody><tr><th>post_id</th><th>profile_id post_date</th><th> </th></tr><tr><td>0001</td><td>0001</td><td>2019-03-12 00:00:00</td></tr><tr><td>0002</td><td>0002</td><td>2019-03-12 00:00:00</td></tr><tr><td>0003</td><td>0001</td><td>2019-03-12 01:00:00</td></tr><tr><td>0004</td><td>0004</td><td>2019-03-12 00:00:00</td></tr></tbody></table>

<table><tbody><tr><th>post_id</th><th>profile_id post_date</th><th> </th></tr><tr><td>0003</td><td>0001</td><td>2019-03-12 01:00:00</td></tr><tr><td>0002</td><td>0002</td><td>2019-03-12 00:00:00</td></tr><tr><td>0004</td><td>0004</td><td>2019-03-12 00:00:00</td></tr></tbody></table>

Answer 1

我想简单地返回每个配置文件所做的最新帖子。

要做到这一点，一种不与group by混在一起的方法是使用带有not exists条件的相关子查询，以确保所选的每条记录都是具有相同profile_id的每组记录中最近的一条（我使用列post_date对记录进行排序）：

SELECT 
    `post_id`, 
    `profile_id`, 
    `post_title`, 
    `post_slug`, 
    `post_date`
FROM $SQLTable_Posts p
WHERE 
    `post_date` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    AND NOT EXISTS (
        SELECT 1
        FROM $SQLTable_Posts p1
        WHERE p1.`profile_id` = p.`profile_id` AND p1.`post_date` > p.`post_date`
    )
ORDER BY `post_date` DESC

注意：我简化了查询，删除了冗长的表前缀，这里不需要这些前缀（和/或可以用短表别名替换）。

如果您碰巧使用的是MySQL8.0，那么使用row_number()，这就很简单了：

SELECT *
FROM (
    SELECT 
        `post_id`, 
        `profile_id`, 
        `post_title`, 
        `post_slug`, 
        `post_date`,
        ROW_NUMBER() OVER(PARTITION BY `profile_id` ORDER BY `post_date` DESC) rn
    FROM $SQLTable_Posts p
    WHERE `post_date` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
) x WHERE rn = 1
ORDER BY `post_date` DESC