如何用PHP和MySQL将重复refID的数据库列数据转为行展示？

原始数据表 #

+----+-------+--------+
| ID | refID |  data  |
+----+-------+--------+
|  1 |  1023 | aaaaaa |
|  2 |  1024 | bbbbbb |
|  3 |  1025 | cccccc |
|  4 |  1023 | ffffff |
|  5 |  1025 | gggggg |
|  6 |  1022 | rrrrrr |
+----+-------+--------+

目标输出格式 #

+----+-------+--------+--------+
| ID | refID |  data  | data2  |
+----+-------+--------+--------+
|  1 |  1023 | aaaaaa | ffffff |
|  2 |  1024 | bbbbbb |        |
|  3 |  1025 | cccccc | gggggg |
|  4 |  1022 | rrrrrr |        |
+----+-------+--------+--------+

当然可以实现，下面提供两种方案，按需选择：

方案一：纯MySQL实现（无需PHP） #

如果你的MySQL版本在8.0及以上，推荐用窗口函数实现，逻辑清晰且高效；低版本可使用自连接方式。

方法1：窗口函数+条件聚合（MySQL 8.0+）

先给每个refID下的记录按ID排序编号，再通过聚合函数把第一条、第二条数据分别放到data和data2列：

SELECT
    MIN(ID) AS ID,
    refID,
    MAX(CASE WHEN rn = 1 THEN data END) AS data,
    MAX(CASE WHEN rn = 2 THEN data END) AS data2
FROM (
    SELECT
        ID,
        refID,
        data,
        ROW_NUMBER() OVER(PARTITION BY refID ORDER BY ID) AS rn
    FROM your_table_name -- 替换成你的表名
) t
GROUP BY refID
ORDER BY ID;

方法2：自连接（兼容MySQL 5.x）

如果你的MySQL版本不支持窗口函数，用自连接筛选每个refID的第一条记录，再关联第二条记录：

SELECT
    t1.ID,
    t1.refID,
    t1.data,
    t2.data AS data2
FROM your_table_name t1
LEFT JOIN your_table_name t2
    ON t1.refID = t2.refID AND t2.ID > t1.ID
WHERE NOT EXISTS (
    SELECT 1 FROM your_table_name t3
    WHERE t3.refID = t1.refID AND t3.ID < t1.ID
)
ORDER BY t1.ID;

方案二：PHP+MySQL结合实现 #

如果需要更灵活的逻辑（比如后续可能扩展更多dataN列），用PHP处理分组会更易维护：

<?php
// 初始化PDO连接（替换成你的数据库信息）
$dbHost = 'localhost';
$dbName = 'your_database';
$dbUser = 'your_username';
$dbPass = 'your_password';

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 查询所有数据，按refID和ID排序，确保同refID的记录连续
$stmt = $pdo->query("SELECT ID, refID, data FROM your_table_name ORDER BY refID, ID");
$allRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 分组处理数据
$mergedData = [];
foreach ($allRows as $row) {
    $refId = $row['refID'];
    if (!isset($mergedData[$refId])) {
        // 第一次遇到该refID，初始化结构
        $mergedData[$refId] = [
            'ID' => $row['ID'],
            'refID' => $refId,
            'data' => $row['data'],
            'data2' => ''
        ];
    } else {
        // 已有该refID的记录，填充data2
        $mergedData[$refId]['data2'] = $row['data'];
    }
}

// 转换为索引数组并按ID排序
$mergedData = array_values($mergedData);
usort($mergedData, function($a, $b) {
    return $a['ID'] - $b['ID'];
});

// 输出示例（这里用HTML表格展示，也可以转成JSON等格式）
echo "<table border='1' cellpadding='8'>";
echo "<tr><th>ID</th><th>refID</th><th>data</th><th>data2</th></tr>";
foreach ($mergedData as $item) {
    echo "<tr>";
    echo "<td>{$item['ID']}</td>";
    echo "<td>{$item['refID']}</td>";
    echo "<td>{$item['data']}</td>";
    echo "<td>{$item['data2']}</td>";
    echo "</tr>";
}
echo "</table>";
?>

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