MySQL分组问题

I would like to show data group by Continent.

I have the following table:

Table: Score

RegionID  | RegionName  | Continent  | Score
----------+-------------+------------+-------
A001      | Hong Kong   | Asia       | 97
A002      | Macau       | Asia       | 94
A003      | Taiwan      | Asia       | 95
A004      | Seoul       | Asia       | 96
A005      | Tokyo       | Asia       | 95
A006      | Osaka       | Asia       | 93
E001      | London      | Europe     | 88
A007      | Beijing     | Asia       | 83
E002      | Paris       | Europe     | 89
U001      | New York    | N. America | 91
U002      | Las Vegas   | N. America | 90
T001      | Sydney      | Australia  | 98

Expected Output:

Asia:
-----------------------------------------
97 - Hong Kong
94 - Macau

...

N. America
-----------------------------------------
91 - New York
90 - Las Vegas

Now I have to separate the SQL command to output this.

I need a faster solution than

SELECT * FROM Score WHERE Continent='Asia'
SELECT * FROM Score WHERE Continent='N. America'

If you select everything in one query:

SELECT * FROM Score WHERE Continent IN ('Asia', 'N. America');

Then, in PHP, you would do as notulyssess suggested and with the result set do:

$data[$row['continent']][] = $row;

Finnally, you would loop through your $data array as such:

for($data as $continent => $entries){
    echo '<b>' . $continent . '</b><br />-----------------------------------------<br />';
    for($entries as $e){
        echo $e['score'] . ' - ' . $e['region_name'] . '<br>';
    }
}

That's not GROUPED data from database. That's simply a sorted list and then you use your programming language (PHP) to show the grouping.

SELECT * FROM Score ORDER BY Continent

Now when you get that result you have to use PHP to show each new continent in a new line and then show its entries. Since you need all the rows, doing that grouping with database only will be an overkill. And you have to create your continent headers in PHP anyway.

SELECT * FROM Score WHERE Continent='Asia' limit 2
UNION
SELECT * FROM Score WHERE Continent='N. America' limit 2

Hope this help you..

For offered solution the query has to be:

SELECT * FROM Score ORDER BY Continent

And the php presentation will be:

<?php

// I've entered the sample data as a array - you'll got it the same after the query.
// I've qvoid the last column from your sample data, because it isn't important for my presentation
$dat=array(
0=>array('RegionID'=>'A001','RegionName'=>'Hong Kong','Continent'=>'Asia'),
1=>array('RegionID'=>'A002','RegionName'=>'Macau','Continent'=>'Asia'),
2=>array('RegionID'=>'A003','RegionName'=>'Taiwan','Continent'=>'Asia'),
3=>array('RegionID'=>'A004','RegionName'=>'Seoul','Continent'=>'Asia'),
4=>array('RegionID'=>'A005','RegionName'=>'Tokyo','Continent'=>'Asia'),
5=>array('RegionID'=>'A006','RegionName'=>'Osaka','Continent'=>'Asia'),
6=>array('RegionID'=>'A007','RegionName'=>'Beijing','Continent'=>'Asia'),
7=>array('RegionID'=>'E001','RegionName'=>'London','Continent'=>'Europe'),
8=>array('RegionID'=>'E002','RegionName'=>'Paris','Continent'=>'Europe'),
9=>array('RegionID'=>'U001','RegionName'=>'New York','Continent'=>'N. America'),
10=>array('RegionID'=>'U002','RegionName'=>'Las Vegas','Continent'=>'N. America'),
11=>array('RegionID'=>'T001','RegionName'=>'Sydney','Continent'=>'Australia'),
);

// Maincode
$sbuf='';
foreach($dat as $da){
    if($da['Continent']!=$sbuff){
        "<br/>".echo $da['Continent']."<br/>-----------------------------------------<br/>";
    }
    echo $da['RegionName']."<br/>";
    $sbuff=$da['Continent'];
}
?>