What is the correct way of retrieving maximum values of all columns in a table with a single query? Thanks.
Clarification: the same query should work on any table, i.e. the column names are not to be hard-coded into it.
解决方案
You're going to have to do it in two steps - one to retrieve the structure of the table, followed by a second step to retrieve the max values for each
In php:
$table = "aTableName";
$columnsResult = mysql_query("SHOW COLUMNS FROM $table");
$maxValsSelect = "";
while ($aColumn = mysql_fetch_assoc($columnsResult)) {
if (strlen($maxValsSelect) > 0) {
//Seperator
$maxValsSelect .= ", ";
}
$maxValsSelect .= "MAX(" . $aColumn['Field'] . ") AS '" . $aColumn['Field'] . "'";
}
//Complete the query
$maxValsQuery = "SELECT $maxValsSelect FROM $table";
$maxValsResult = mysql_query($maxValsQuery);
//process the results....