MySQL 按字段分組顯示數據：PDO 實現分組查詢與雙向排序完整範例

需求

表 group 有欄位 conf_id、time 等。需按 conf_id 分組，每組取最早與最晚的一條記錄。

PHP PDO 實現

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

-- 1. 取所有分組 ID
$sql = "SELECT conf_id FROM `group` GROUP BY conf_id";
$stmt = $db->query($sql);
$groups = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($groups as $g) {
    $cid = $g['conf_id'];
    echo $cid . "<br>";

    -- 2. 正序取第一條
    $sql1 = "SELECT * FROM `group` WHERE conf_id = ? ORDER BY time ASC LIMIT 1";
    $st1 = $db->prepare($sql1);
    $st1->execute([$cid]);
    $first = $st1->fetch(PDO::FETCH_ASSOC);
    print_r($first);

    -- 3. 倒序取第一條
    $sql2 = "SELECT * FROM `group` WHERE conf_id = ? ORDER BY time DESC LIMIT 1";
    $st2 = $db->prepare($sql2);
    $st2->execute([$cid]);
    $last = $st2->fetch(PDO::FETCH_ASSOC);
    print_r($last);
}
?>

優化建議

• 使用 UNION ALL 合併為單查詢，減少往返：

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

（需 MySQL 8.0+ 支援窗口函數）

• 若版本較低，可用 GROUP_CONCAT + SUBSTRING_INDEX 技巧，但可讀性較差。