php - 如何在PHP中合并具有相同ID的数据？

我的选择返回的值是这样的：


它可以将两行合并为一个吗？如果是这样，有可能在php或Tsql中？
就像是：


这是我的选择：

           SELECT TRM_ID_TURMA
                 , TRM_HORA_INICIO
                 , TRM_HORA_FIM
                 , TRM_DIA
                 , TRM_VAGAS
                 , MGN_NOME
                 , PROF.USU_NOME AS PROFESSOR
                 , ALU_MATRICULA
                 , USU.USU_NOME NOMEALUNO
              FROM [MGB].[dbo].[TB_TURMA]
             INNER JOIN TB_USUARIO PROF ON PROF.USU_ID_USUARIO = TRM_ID_PROFESSOR
             INNER JOIN TB_NIVEL_TURMA ON NTR_ID_NIVEL_TURMA = TRM_ID_NIVEL
             INNER JOIN TB_MGB_NIVEL ON MGN_ID_MGB_NIVEL = NTR_ID_MGB_NIVEL
             INNER JOIN TB_LOCAL ON LOC_ID_LOCAL = TRM_ID_LOCAL
              LEFT JOIN TB_TURMA_ALUNO ON TAL_ID_TURMA = TRM_ID_TURMA
              LEFT JOIN TB_ALUNO ON ALU_ID_ALUNO = TAL_ID_ALUNO
              LEFT JOIN TB_USUARIO USU ON USU.USU_ID_USUARIO = ALU_ID_USUARIO
             WHERE TRM_ID_ACADEMIA = 27
               AND TRM_STATUS >= 0
               AND (TAL_STATUS IS NULL OR TAL_STATUS >= 0)
             ORDER BY TRM_HORA_INICIO,  TRM_HORA_FIM, TRM_DIA, ALU_MATRICULA, NOMEALUNO

最佳答案

改写，我想你说的是，当TRM_ID_TURMA相同时，选择一行，并加入（用逗号）ALU_MATRICULA和NOMEALUNO的内容。

那正确吗？

如果是这样，我认为您应该在PHP中解决它，因为它比SQL容易得多（无论您碰巧使用哪种方言，这对我来说都是个谜，我看到mysql，sql，oracle和tsql）。

假设您已运行查询，并将结果存储在关联的结果集中，即数组，然后尝试执行以下操作：

$RS = ......your query that results in an array.......
/*
$RS looks like:
array(
 [0] => array("TRM_ID_TURMA"=>48, "TRM_HORA_INICIO" => "10:00:00 000000", ..etc..),
 [1] => array("TRM_ID_TURMA"=>49, "TRM_HORA_INICIO" => "10:00:00 000000", ..etc..)
 [2] => etc etc
)
*/

$tmp = array();
foreach ($RS as $one){
  // $tmp used TRM_ID_TURMA as key, and row as content
  if (isset($tmp[$one["TRM_ID_TURMA"]])){
    // TRM_ID_TURMA already used, add to the fields ALU_MATRICULA and NOMEALUNO.
    $tmp[$one["TRM_ID_TURMA"]]["ALU_MATRICULA"] .= ", " . $one["ALU_MATRICULA"];
    $tmp[$one["TRM_ID_TURMA"]]["NOMEALUNO"] .= ", " . $one["NOMEALUNO"];
  } else {
    $tmp[$one["TRM_ID_TURMA"]] = $one;
  }
}

现在，您想要的结果在$ tmp中（以TRM_ID_TURMA作为键）