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

问题描述：

My select returns values like this:

It can merge the two lines into one? If so, it is possible in php, or Tsql?
Something like:

This is my select:

           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

我的选择返回如下值： p >

它可以将两条线合并为一条线？如果是这样，有可能在php或Tsql中？
类似于： p>

这是我的选择：

  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＆gt; = 0 
 AND（TAL_STATUS为NULL或TAL_STATUS＆gt; = 0）
 ORDER  BY TRM_HORA_INICIO，TRM_HORA_FIM，TRM_DIA，ALU_MATRICULA，NOMEALUNO 
  code>  pre> 
  div>

答

Rephrased, I think you say, When TRM_ID_TURMA is the same, pick one row, and join (with comma's) the contents of ALU_MATRICULA and NOMEALUNO.

Is that right?

If so, I think you should solve it in PHP, because that is much easier than in SQL (whichever dialect you happen to use is a mystery to me, I see mysql, sql, oracle and tsql).

Suppose you have run your query, and stored your result in an associative resultset, an array, then try something like this:

$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;
  }
}

And your desired result is now in $tmp (with TRM_ID_TURMA as keys)

答

In mysql, take a look at the GROUP_CONCAT function.

SELECT TRM_ID_TURMA, GROUP_CONCAT(USU.USU_NOME NOMEALUNO)
FROM [MGB].[dbo].[TB_TURMA]
GROUP BY TRM_ID_TURMA

Be aware of group_concat_max_len, since your result will be truncated if it exceeds this length.

答

Please read THIS ARTICLE It shows how to concatenate field values with "FOR XML" this code comes from this article:

USE AdventureWorks2008R2
SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

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

相关推荐