多表关联查询后如何将重复数据整理为目标关联数组？

方案1：单SQL查询+GROUP_CONCAT聚合，PHP拆分字段 #

先调整SQL语句，用GROUP_CONCAT把多对多关联的用户名、诊断信息合并为字符串，避免返回重复的文档主数据行，之后在PHP中拆分字符串为子数组。

SQL示例 #

SELECT 
    d.*,
    GROUP_CONCAT(DISTINCT u.username SEPARATOR '|') AS owner_names,
    GROUP_CONCAT(DISTINCT d1.diagnosis_name SEPARATOR '|') AS d_diagnoses,
    GROUP_CONCAT(DISTINCT d2.diagnosis_name SEPARATOR '|') AS a_diagnoses
FROM DocumentsTable d
LEFT JOIN DocOwnersTable do ON d.DocID = do.DocID
LEFT JOIN users u ON do.UserID = u.UserID
LEFT JOIN DocDDiagnosesTables ddt ON d.DocID = ddt.DocID
LEFT JOIN Diagnoses d1 ON ddt.DiagnosisID = d1.DiagnosisID
LEFT JOIN DocADiagnosesTable dat ON d.DocID = dat.DocID
LEFT JOIN Diagnoses d2 ON dat.DiagnosisID = d2.DiagnosisID
WHERE d.DocID = :DocID
GROUP BY d.DocID;

这里用LEFT JOIN防止因无关联用户/诊断丢失文档数据，DISTINCT去除重复的用户名或诊断，SEPARATOR选一个不会出现在内容里的分隔符（若内容含|，可替换为###等特殊字符）。

PHP处理代码 #

// 假设已完成PDO语句准备与$DocID绑定
$stmt->execute(['DocID' => $DocID]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// 拆分聚合字段为子数组
$result['owners'] = !empty($result['owner_names']) ? explode('|', $result['owner_names']) : [];
$result['d_diagnoses_list'] = !empty($result['d_diagnoses']) ? explode('|', $result['d_diagnoses']) : [];
$result['a_diagnoses_list'] = !empty($result['a_diagnoses']) ? explode('|', $result['a_diagnoses']) : [];

// 移除原始聚合字段（可选）
unset($result['owner_names'], $result['d_diagnoses'], $result['a_diagnoses']);

最终$result即为包含子数组的关联数组，满足视图格式化需求。

方案2：分多次查询组装数组 #

若不想使用聚合函数，可分三次查询：先获取文档主数据，再查询关联用户，最后查询关联诊断，手动组装成目标结构。

步骤1：查询文档主数据 #

SELECT * FROM DocumentsTable WHERE DocID = :DocID;

$stmt = $pdo->prepare("SELECT * FROM DocumentsTable WHERE DocID = :DocID");
$stmt->execute(['DocID' => $DocID]);
$doc = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$doc) {
    // 处理文档不存在的场景
    exit;
}

步骤2：查询关联用户名 #

SELECT u.username 
FROM DocOwnersTable do
JOIN users u ON do.UserID = u.UserID
WHERE do.DocID = :DocID;

$stmt = $pdo->prepare("SELECT u.username FROM DocOwnersTable do JOIN users u ON do.UserID = u.UserID WHERE do.DocID = :DocID");
$stmt->execute(['DocID' => $DocID]);
$doc['owners'] = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // 直接获取用户名数组

步骤3：查询关联诊断信息 #

-- 查询DocDDiagnosesTables关联的诊断
SELECT d.* FROM DocDDiagnosesTables ddt JOIN Diagnoses d ON ddt.DiagnosisID = d.DiagnosisID WHERE ddt.DocID = :DocID;
-- 查询DocADiagnosesTable关联的诊断
SELECT d.* FROM DocADiagnosesTable dat JOIN Diagnoses d ON dat.DiagnosisID = d.DiagnosisID WHERE dat.DocID = :DocID;

// 获取D类型诊断
$stmt = $pdo->prepare("SELECT d.* FROM DocDDiagnosesTables ddt JOIN Diagnoses d ON ddt.DiagnosisID = d.DiagnosisID WHERE ddt.DocID = :DocID");
$stmt->execute(['DocID' => $DocID]);
$doc['d_diagnoses'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 获取A类型诊断
$stmt = $pdo->prepare("SELECT d.* FROM DocADiagnosesTable dat JOIN Diagnoses d ON dat.DiagnosisID = d.DiagnosisID WHERE dat.DocID = :DocID");
$stmt->execute(['DocID' => $DocID]);
$doc['a_diagnoses'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

最终$doc就是包含子数组的目标结构，无重复数据。

原方法无效的原因 #

多表INNER JOIN后，每一个关联的用户或诊断都会生成新行，fetchAll会返回大量重复的文档主数据行，而array_diff这类函数无法处理这种结构——因为重复的是整行数据，仅关联字段不同。上述两种方案要么通过SQL聚合减少返回行数，要么分查询从根源避免重复。

内容的提问来源于stack exchange，提问作者dr.help