我搜索了较旧的线程，但尚未找到以下问题的解决方案:是否可以在不诉诸嵌套查询的情况下对 mysql 结果进行分组和排列？

请看下面的示例。

我有:

Month Jan, Location USA, Program DDD
Month Jan, Location UK, Program EEE
Month Jan, Location USA, Program LLL
Month FEB, Location UAE, Program EEE
Month FEB, Location USA, Program DDD
Month FEB, Location UK, Program MMM
Month MAR, Location USA, Program FFF
Month MAR, Location UAE, Program FFF
Month MAR, Location UK, Program FFF

我希望他们这样显示:

                +---------++---------++---------+
                |Month Jan||Month Feb||Month Mar|
+---------------+---------++---------++---------+
|Location USA   |         ||         ||         |
+---------------+---------++---------++---------+
|               |DDD      ||DDD      ||FFF      |
+---------------+---------++---------++---------+
|               |EEE      ||         ||         |
+---------------+---------++---------++---------+
|               |LLL      ||         ||         |
+---------------+---------++---------++---------+
|Location UK    |         ||         ||         |
+---------------+---------++---------++---------+
|               |EEE      ||MMM      ||FFF      |
+---------------+---------++---------++---------+
|Location UAE   |         ||         ||         |
+---------------+---------++---------++---------+
|               |         ||EEE      ||FFF      |
+---------------+---------++---------++---------+

我已尝试对查询进行分组，但无法将其呈现到 HTML 表格。

SELECT 
    event.`event_id`,
    event.`event_program_id`,
    event.`event_month`, 
    event.`event_location_id`,
    location.`location_name`,
    program.`program_name`,
    program.`program_shortname`
FROM 
    `event`
LEFT JOIN
    `location`
ON
    event.`event_location_id` = location.`location_id`
LEFT JOIN
    `program`
ON
    event.`event_program_id` = program.`program_id`;

现在它的列表是这样的:

    column1 column2 column3
line1   34              
line2   34              
line3           34  
line5       34          

我想要的地方是这样的:

    column1 column2 column3 
line1   34      34
                34      34
                34        

line2   34
        34      34      34              
line3           34      
line5       34          

最佳答案

如果您在预期结果中有预定义的列数(例如一年中的月份)，您可以对每一列执行一些内部查询来实现此目的。

我有一些基于 SQL 的报告使用像这样的子查询的经验，在一天结束时，这个解决方案管理起来很痛苦 :) 今天我只是读取数据，存储在内存中的矩阵中，然后最后根据矩阵生成结果，以后更容易改变。

干杯!

这里是PHP的例子

<pre>
<?php
$columns = array();
$data = array();

// add the data from the database
$data["line1"]["column1"] += 34;
$data["line2"]["column1"] += 34;
$data["line3"]["column4"] += 34;
$data["line5"]["column2"] += 34;

$data["line1"]["column1"] += 34;
$data["line3"]["column4"] += 34;

// find the columns
foreach ($data as $line => $column) {
    foreach ($column as $cname => $value) {
        if( ! in_array($cname, $columns) ){
            $columns[] = $cname;
        }
    }
}

sort($columns);

// display column names
echo " \t";
foreach ($columns as $index => $cname) {
    echo $cname . "\t";
}
echo "\n";

// display the data
foreach ($data as $line => $column) {
    echo $line . "\t";
    foreach ($columns as $index => $cname) {
        echo $column[$cname] . "\t";
    }
    echo "\n";
}
?>
</pre>

这是解决问题的另一种方法

<pre>
<?php
$columns = array();
$data = array();

// add the data from the database
// sort the data in the SQL
$data[] = array("l" => "line1", "c"=> "column1", "v" => "AAA");
$data[] = array("l" => "line1", "c"=> "column1", "v" => "BBB");
$data[] = array("l" => "line1", "c"=> "column3", "v" => "CCC");
$data[] = array("l" => "line2", "c"=> "column2", "v" => "AAA");
$data[] = array("l" => "line3", "c"=> "column2", "v" => "AAA");

// find the columns
foreach ($data as $line => $column) {
    if( ! in_array($column["c"], $columns) ){
        $columns[] = $column["c"];
    }
}

sort($columns);

// display column names
echo " \t";
foreach ($columns as $index => $cname) {
    echo $cname . "\t";
}
echo "\n";

$name = '';
$count = 0;
// display the data
foreach ($data as $line => $column) {
    if( $column['l'] == $name ){
        $count ++;
    } else {
        $name = $column['l'];
        $count = 0;
    }

    if( $count == 0 ){
        echo $name . "\t";
    } else {
        echo " \t";
    }   

    foreach ($columns as $index => $cname) {
        if( $column['c'] == $cname ){
            echo $column['v'] . "\t";       
        } else {
            echo " \t";
        }
    }
    echo "\n";
}
?>
</pre>

其他使用 html 作为输出的引用

<table border="1">
<?php
$columns = array();
$data = array();

// add the data from the database
// sort the data in the SQL
$data[] = array("l" => "line1", "c"=> "column1", "v" => "AAA");
$data[] = array("l" => "line1", "c"=> "column1", "v" => "BBB");
$data[] = array("l" => "line1", "c"=> "column3", "v" => "CCC");
$data[] = array("l" => "line2", "c"=> "column2", "v" => "AAA");
$data[] = array("l" => "line3", "c"=> "column2", "v" => "AAA");

// find the columns
foreach ($data as $line => $column) {
    if( ! in_array($column["c"], $columns) ){
        $columns[] = $column["c"];
    }
}

sort($columns);

// display column names
echo "<tr>";
echo "<td>&nbsp;</td>";
foreach ($columns as $index => $cname) {
    echo "<td>".$cname."</td>";
}
echo "</tr>";

$name = '';
$count = 0;
// display the data
foreach ($data as $line => $column) {
    echo "<tr>";

    if( $column['l'] == $name ){
        $count ++;
    } else {
        $name = $column['l'];
        $count = 0;
    }

    if( $count == 0 ){
        echo "<td>".$name."</td>";
    } else {
        echo "<td>&nbsp;</td>";
    }   

    foreach ($columns as $index => $cname) {
        if( $column['c'] == $cname ){
            echo "<td>".$column['v']."</td>";
        } else {
            echo "<td>&nbsp;</td>";
        }
    }
    echo "</tr>";
}
?>
</table>

关于php - 使用嵌套格式对 Mysql 查询进行分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14933485/