PHP + MySQL：依欄位分組顯示資料並取得各組首尾筆

需求場景

資料表 group 依 conf_id 分組，需顯示每組的第一筆與最後一筆記錄（按 time 排序）。

完整程式碼範例

<?php
$dsn = "mysql:host=localhost;dbname=group";
$db = new PDO($dsn, 'root', '');
$db->exec('SET NAMES utf8');

// 取得所有不重複的 conf_id
$sql = "SELECT conf_id FROM `group` GROUP BY conf_id";
$pre = $db->query($sql);
$result_arr = $pre->fetchAll(PDO::FETCH_ASSOC);

foreach ($result_arr as $v) {
    $conf_id = $v['conf_id'];
    echo $conf_id . "<br>";

    // 正序取第一筆
    $sql1 = "SELECT * FROM `group` WHERE conf_id = :cid ORDER BY time LIMIT 1";
    // 倒序取最後一筆
    $sql2 = "SELECT * FROM `group` WHERE conf_id = :cid ORDER BY time DESC LIMIT 1";

    $stmt1 = $db->prepare($sql1);
    $stmt1->execute([':cid' => $conf_id]);
    $first = $stmt1->fetch(PDO::FETCH_ASSOC);

    $stmt2 = $db->prepare($sql2);
    $stmt2->execute([':cid' => $conf_id]);
    $last = $stmt2->fetch(PDO::FETCH_ASSOC);

    print_r($first);
    print_r($last);
}
?>

關鍵優化說明

• 使用 LIMIT 1 避免撈取整組資料，大幅降低記憶體佔用
• 採用預處理語句防止 SQL 注入
• 若資料量極大，建議在 conf_id 與 time 上建立複合索引 INDEX idx_conf_time (conf_id, time)

進階：單一查詢完成（MySQL 8.0+）

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY conf_id ORDER BY time) AS rn_asc,
           ROW_NUMBER() OVER (PARTITION BY conf_id ORDER BY time DESC) AS rn_desc
    FROM `group`
)
SELECT * FROM ranked WHERE rn_asc = 1 OR rn_desc = 1;