select bj, max(zf) as maxnum, sum(if(zf>=590 and zf<600, 1, 0)) as n600, sum(if(zf>=580 and zf<590, 1, 0)) as n590, sum(if(zf>=570 and zf<580, 1, 0)) as n580, sum(if(zf>=560 and zf<570, 1, 0)) as n570, sum(if(zf>=550 and zf<560, 1, 0)) as n560, sum(if(zf>=540 and zf<550, 1, 0)) as n550, sum(if(zf>=530 and zf<540, 1, 0)) as n540, sum(if(zf>=520 and zf<530, 1, 0)) as n530, sum(if(zf>=510 and zf<520, 1, 0)) as n520, sum(if(zf>=500 and zf<510, 1, 0)) as n510, sum(if(zf>=490 and zf<500, 1, 0)) as n500, sum(if(zf>=480 and zf<490, 1, 0)) as n490, sum(if(zf>=470 and zf<480, 1, 0)) as n480, sum(if(zf>=460 and zf<470, 1, 0)) as n470, sum(if(zf>=450 and zf<460, 1, 0)) as n460 from mytable group by bj查询后直接输出即可sql语句可用下面的代码产生 function fd($s, $e, $d=10) { $r = array(); for($i=$s; $i<$e; $i+=$d) { $p = $i + $d; $r[] = "sum(if(zf>=$i and zf<$p, 1, 0)) as n$p"; } return join(",\r\n", array_reverse($r)); }echo $sql = "select bj, max(zf) as maxnum, ". fd(450, 600, 10) . " from mytable group by bj";
//建议不要想着数据库一步到位,可以用php对查询的数据进行加工,//如果这样,先初始化14个班级的数组
$result = array();
for($i=0;$i<14;$i++)
{
$result[$i] = array('class' => $i+1, 'highscore' => 0, '600' => 0, '590 => 0, ……');
}
//接着数据库搜索所有的数据,循环判断一遍,看需要改变$result的哪个值
$result = array();
for($i=0;$i<14;$i++){
$result[$i] = array('class' => $i+1, 'highscore' => 0, '600' => 0, '590 => 0, ……');
}for($i=0;$i<14;$i++){
$query = "select bj,zf div 10,count(*) from mytable where zf>=480 and zf<600 where bj='$i' order by bj asc,zf desc";//不知道max值能不能在这里直接求出来
$tmp = mysql_query($query);
$result[$i] = mysql_fetch_row($tmp);
}
?>
include 'config.inc.php';
$connection = mysql_connect($hostname, $dbusername, $dbpassword);
mysql_select_db($database);$result = array();
for($i=0;$i<14;$i++)
{
$result[$i] = array('class' => $i+1, 'highscore' => 0, '600' => 0, '590' => 0, '580' => 0, '570' => 0, '560' => 0, '550' => 0, '540' => 0, '530' => 0, '520' => 0, '510' => 0, '500' => 0, '490' => 0, '480' => 0);
}$query = "select bj,zf from mytable where 1";//有年份就加上年份限制
$res = mysql_query($query);
while ($r = mysql_fetch_assoc($res))
{
//是否需要改变该班级最高分
if($r['zj'] > $result[$r['bj'] - 1]['highscore'])
{
$result[$r['bj'] - 1]['highscore'] = $r['zj'];
}
//分情况,哪个分数段+1…………
$key = 480;
if($r['zj'] >=600)
{
$key = 600;
}
else if($r['zj'] >=480)
{
$key = float($r['zj']/10) * 10;
}
else
{
//分数低于480的怎么办?
//这样写下来会被加到480组里,要改动在这里改好了
}
$result[$r['bj'] - 1][$key]++;
}print_r($result);//输出看看是你要的结果么
$key = intval($r['zj']/10) * 10;
sum(if(zf>=580 and zf<590, 1, 0)) as n590,
sum(if(zf>=570 and zf<580, 1, 0)) as n580,
sum(if(zf>=560 and zf<570, 1, 0)) as n570,
sum(if(zf>=550 and zf<560, 1, 0)) as n560,
sum(if(zf>=540 and zf<550, 1, 0)) as n550,
sum(if(zf>=530 and zf<540, 1, 0)) as n540,
sum(if(zf>=520 and zf<530, 1, 0)) as n530,
sum(if(zf>=510 and zf<520, 1, 0)) as n520,
sum(if(zf>=500 and zf<510, 1, 0)) as n510,
sum(if(zf>=490 and zf<500, 1, 0)) as n500,
sum(if(zf>=480 and zf<490, 1, 0)) as n490,
sum(if(zf>=470 and zf<480, 1, 0)) as n480,
sum(if(zf>=460 and zf<470, 1, 0)) as n470,
sum(if(zf>=450 and zf<460, 1, 0)) as n460 from mytable group by bj查询后直接输出即可sql语句可用下面的代码产生
function fd($s, $e, $d=10) {
$r = array();
for($i=$s; $i<$e; $i+=$d) {
$p = $i + $d;
$r[] = "sum(if(zf>=$i and zf<$p, 1, 0)) as n$p";
}
return join(",\r\n", array_reverse($r));
}echo $sql = "select bj, max(zf) as maxnum, ". fd(450, 600, 10) . " from mytable group by bj";
array('bj'=>'1','fs'=>'49','rs'=>'2','leiji'=>'2'),
array('bj'=>'1','fs'=>'48','rs'=>'1','leiji'=>'3'),
array('bj'=>'10','fs'=>'49','rs'=>'8','leiji'=>'8'),
array('bj'=>'10','fs'=>'48','rs'=>'3','leiji'=>'11'),
array('bj'=>'10','fs'=>'47','rs'=>'2','leiji'=>'13'),
array('bj'=>'10','fs'=>'46','rs'=>'4','leiji'=>'17'),
array('bj'=>'10','fs'=>'45','rs'=>'4','leiji'=>'21'),
);$bj=array();
foreach($data as $item){
$bj[$item[bj]][$item[fs]]=$item;//以bj ,fs为2维数组.
}echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<table border=1>";
echo "<tr>";
echo "<td></td>";
echo "<td>最高分</td>";
for($i=60;$i>=45;$i--){
echo "<td>{$i}0</td>";
}
echo "</tr>\r\n";
$header=false;foreach($bj as $k=>$item){
echo "<tr>";
echo "<td>高一($k)</td>";
echo "<td>xxx</td>";
for($i=60;$i>=45;$i--){
$td=$item[$i][leiji]?$item[$i][leiji]:' ';
echo "<td>$td</td>";
}
echo "</tr>\r\n";
}
echo "</table>";?>