如何在关联表查询中结合PDO::FETCH_GROUP返回3条最近航程记录

你说得没错，直接用全局的ORDER BY和LIMIT确实没法实现按分组限制条数——PDO的FETCH_GROUP是先获取所有查询结果再做分组，全局LIMIT会截断整个结果集，而不是每个分组内的结果。我们需要在SQL层面先给每个航程类型的航程排序并筛选出前3条，再进行关联查询。

核心思路：用窗口函数实现分组内的TOP N筛选 #

我们可以借助ROW_NUMBER()窗口函数，按航程类型分组，对每个分组内的航程按voyage_startDate（离今日最近的在前）排序，然后只保留行号≤3的记录。

修改后的PHP代码实现 #

public function getVoyageTypesWithTrips() {
    // 使用窗口函数筛选每个航程类型下最近的3条航程
    $sql = '
        SELECT vt.voyagetype_name, vt.voyagetype_description, vt.voyagetype_image, 
               v.voyage_id, v.voyage_name, v.voyage_startDate
        FROM voyagetypes vt
        LEFT JOIN (
            SELECT 
                voyage_id, voyage_name, voyage_startDate, voyage_type,
                -- 按航程类型分组，按日期从近到远排序并分配行号
                ROW_NUMBER() OVER (
                    PARTITION BY voyage_type 
                    ORDER BY voyage_startDate ASC
                ) AS row_num
            FROM voyages
            -- 可选：只保留今日及之后的航程，符合你举例的"下一周/明日"需求
            WHERE voyage_startDate >= CURDATE()
        ) v ON vt.voyagetype_id = v.voyage_type AND v.row_num <= 3
        WHERE vt.voyagetype_deleted != 1
        ORDER BY vt.voyagetype_name, v.voyage_startDate ASC
    ';
    
    $this->db->query($sql);
    $results = $this->db->resultSetGrouped();
    return $results;
}

// 原有的resultSetGrouped方法可以继续使用
public function resultSetGrouped() {
    $this->execute();
    return $this->statement->fetchAll(PDO::FETCH_GROUP);
}

关键细节说明 #

1. 窗口函数的作用：ROW_NUMBER() OVER (PARTITION BY voyage_type ORDER BY voyage_startDate ASC)会给每个voyage_type下的航程按日期从近到远分配行号，最近的为1，以此类推。
2. 子查询筛选：在子查询中我们只保留row_num <=3的记录，确保每个航程类型最多返回3条最近的航程。
3. LEFT JOIN的保留：外层用LEFT JOIN确保即使某个航程类型没有符合条件的航程（比如你说的分类3），依然会返回该类型的基础信息，对应的航程数组为空。
4. 日期过滤的灵活性：子查询中的WHERE voyage_startDate >= CURDATE()可以只保留今日及之后的航程，如果需要包含过去的航程，直接去掉这个条件即可。

为什么原来的方法行不通？ #

你之前尝试的全局ORDER BY+LIMIT会把整个查询结果的总条数限制为3，而不是每个航程类型下的3条。PDO的FETCH_GROUP是在获取所有结果后再分组，所以全局限制无法实现分组内的TOP N需求，必须在SQL层面先完成分组筛选。

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