MYSQL + PHP 将两行数据显示为单列 [英] MYSQL + PHP to display data from two rows as a single column

问题描述

我有一个包含以下格式数据的表格

I have a table with data in a following format

region   gender  age_group  population 

Dallas    M        1          200  
Dallas    F        1          500 
NY        M        1          320 
NY        F        1          310 
Dallas    M        2          300 
Dallas    F        2          600 
NY        M        2          400 
NY        F        2          800 

我正在寻找以下列格式输出数据的最佳查询

I'm looking for the best query to output my data in the following format

region/age_group      1           2
Gender             M     F       M    F
Dallas             200   500    300  600
NY                 320   310    400  800

我的查询仅返回顶部列和区域.不确定如何将顶部列细分为性别，如上所示

my query returns only the top colum and region. Not sure how to sub divide the top colum into genders as appearing above

SELECT distinct (region), a.group_one, b.group_two 
FROM city_populations c  
LEFT JOIN city_populations AS a ON a.age_group=1 AND c.age_group=a.age_group  
LEFT JOIN city_populations AS b ON b.age_group=2 AND c.age_group=a.age_group  

推荐答案

//首先我从数据库中获取所有数据

//First i fetched all data from database

$result = mysql_query("select * from test2")or die(mysql_error());
$regions = array();
$age_groups = array();
while($det = mysql_fetch_array($result)){

    if(!in_array($det['region'], $regions)){
        $regions[] = $det['region'];
    }
    if(!in_array($det['age_group'], $age_groups)){
        $age_groups[] = $det['age_group'];
    }
    $rows[] = $det;
}

//然后在php中处理以显示您想要的

//then processed in php to show as you wanted

echo "<table>";
echo "<tr><td>Age group</td>";
foreach($age_groups as $keyAG => $valueAG){
    echo "<td colspan='2'>$valueAG</td>";
}
echo "</tr>";
echo "<tr><td>Gender</td>";
foreach($age_groups as $keyAG => $valueAG){
    echo "<td>M</td>";
    echo "<td>F</td>";
}
echo "</tr>";
foreach($regions as $keyR => $valueR){

    echo "<tr>";
    echo "<td>".$valueR."</td>";
    foreach($age_groups as $keyAG => $valueAG){

        foreach($rows as $keyROWS => $valueROWS){
            if($valueROWS['region'] == $valueR && $valueROWS['age_group'] == $valueAG){
                if($valueROWS['gender'] == 'M'){
                    echo "<td>".$valueROWS['population']."</td>";
                }
                elseif($valueROWS['gender'] == 'F'){
                    echo "<td>".$valueROWS['population']."</td>";
                }
            }
        }
        $data[$valueR][$valueAG] = array($maleValue, $femaleValue);

    }
    echo "</tr>";
}
echo "</table>";

注意:强烈建议使用 mysqli_* 而不是 mysql.

Note: It is highly recommended to use mysqli_* instead of mysql.

这篇关于MYSQL + PHP 将两行数据显示为单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！