MySQL分组查询：获取Facebook消息系统最新对话行

核心需求拆解 #

• 排除Sender ID为12345的记录
• 按发送方（Sender ID）分组，仅保留每组中最新的消息（按行ID降序取最大ID）
• 结果按消息新旧程度排序（最新的在列表顶部）
• 支持分页逻辑

原SQL的问题 #

• 子查询中GROUP BY senderid时未指定取分组内最大ID，数据库会随机返回分组内某条记录，导致结果无序
• 外层用ORDER BY is_read ASC排序，是按已读状态排序，而非消息的新旧程度

正确的SQL实现 #

通过先获取每个Sender ID的最大行ID，再关联原表取对应记录的方式实现需求，同时保留分页：

$fb_page_id = "12345"; // 替换为你的企业页ID
$page_num = 0; // 当前页码，从0开始
$perpage = 10; // 每页条数

$sql = "
    SELECT f.id AS `Row ID`, f.senderid AS `Sender ID`, f.is_read
    FROM FB f
    INNER JOIN (
        SELECT senderid, MAX(id) AS max_id
        FROM FB
        WHERE senderid != '$fb_page_id'
        GROUP BY senderid
    ) AS latest ON f.senderid = latest.senderid AND f.id = latest.max_id
    ORDER BY f.id DESC
    LIMIT " . ($page_num * $perpage) . ", $perpage;
";

代码说明 #

1. 子查询逻辑：
   • 过滤掉Sender ID为12345的记录
   • 按senderid分组，获取每个发送方的最大行ID（即最新消息的ID）
2. 关联原表：
   • 通过senderid和max_id关联原表，精准获取每个发送方的最新消息记录
3. 排序与分页：
   • ORDER BY f.id DESC确保最新消息排在顶部
   • 保留原有分页计算逻辑，实现分页查询

测试结果验证 #

用你提供的示例数据执行该SQL，会得到与期望一致的结果：

Row ID | Sender ID | is_read
4      | 67890     | 0
2      | 23232     | 0
6      | 55555     | 1

额外优化建议 #

• 为senderid和id建立联合索引，提升分组和关联查询的性能
• 避免直接拼接变量到SQL中，建议使用预处理语句防止SQL注入，示例：

// PDO预处理示例
$stmt = $pdo->prepare("
    SELECT f.id AS `Row ID`, f.senderid AS `Sender ID`, f.is_read
    FROM FB f
    INNER JOIN (
        SELECT senderid, MAX(id) AS max_id
        FROM FB
        WHERE senderid != ?
        GROUP BY senderid
    ) AS latest ON f.senderid = latest.senderid AND f.id = latest.max_id
    ORDER BY f.id DESC
    LIMIT ?, ?;
");
$stmt->execute([$fb_page_id, $page_num * $perpage, $perpage]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

内容的提问来源于stack exchange，提问作者EagleTalonTim