php - MYSQL 和 PHP 对结果进行分组

Question

我想按 Comp 在各种 html 表组中显示以下数据库。

TeamA         TeamB             CompKenya     Namibia       Africa   World Cup - QualZimbabwe      Mozambique       Africa World Cup - QualCoventry City Colchester Utd   England - League One Bray Wanderers UCD League Of   Ireland - Premier DivisionDundalk    Drogheda United  League Of Ireland - Premier Division

example table 1

Comp: Africa World Cup - QualKenya     NamibiaZimbabwe  Mozambique 

Example table 2

England - League OneCoventry City Colchester Utd 

etc.

I tried

SELECT GROUP_CONCAT(Comp) as Comp 
  FROM database 
 Group By Comp`

但运气不好。

Answer 1

你在找这个吗？

SELECT comp,
       GROUP_CONCAT(CONCAT(TeamA, '|', TeamB)) details
  FROM table1
 GROUP BY comp

输出:

+--------------------------------------+--------------------------------------------+| comp                                 | details                                    |+--------------------------------------+--------------------------------------------+| Africa World Cup - Qual              | Kenya|Namibia,Zimbabwe|Mozambique          || England - League One                 | Coventry City|Colchester Utd               || League Of Ireland - Premier Division | Bray Wanderers|UCD,Dundalk|Drogheda United |+--------------------------------------+--------------------------------------------+

Here is SQLFiddle demo

If needed you can change delimiters both in CONCAT() and GROUP_CONCAT() from comma , and pipe | respectively. You can easily explode() details values while iterating over the result set.

Simplified php part using PDO might look like this

$db = new PDO('mysql:host=localhost;dbname=test', 'user', 'userpwd');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


$sql = "SELECT comp,
               GROUP_CONCAT(CONCAT(TeamA, '|', TeamB)) details
               FROM table1
         GROUP BY comp";

$query = $db->prepare($sql);
$query->execute();
$rows = $query->fetchall(PDO::FETCH_ASSOC);

$query = null;
$db = null;

foreach($rows as $row) {
    echo $row['comp'] . '</br>';
    $details = explode(',', $row['details']);
    foreach($details as $detail) {
        list($teama, $teamb) = explode('|', $detail);
        echo $teama . ' - ' .$teamb . '</br>';
    }
    echo '</br>';
}

输出:

Africa World Cup - QualKenya - NamibiaZimbabwe - MozambiqueEngland - League OneCoventry City - Colchester UtdLeague Of Ireland - Premier DivisionBray Wanderers - UCDDundalk - Drogheda United