bj是班级,zf是总分
select count(*) t,bj,CAST(zf/10 as SIGNED) fs from mytable
where zf>=510 and zf<=600 group by bj,fs我想求出总分从510到600分各分数段的累计人数,不知道以上sql语句要怎么改?
select count(*) t,bj,CAST(zf/10 as SIGNED) fs from mytable
where zf>=510 and zf<=600 group by bj,fs我想求出总分从510到600分各分数段的累计人数,不知道以上sql语句要怎么改?
SELECT bj,CAST(zf/10 as SIGNED) fs,zf from mytable WHERE zf>=510 AND zf<=600
) GROUP BY bj,fs ORDER BY bj
试试这个
SELECT bj,CAST(zf/10 as SIGNED) fs,zf from mytable WHERE zf>=510 AND zf<=600,
然后在php中弄个循环,统计数量
select count(*) t,bj,CAST(zf/10 as SIGNED) fs from mytable
where zf>=510 and zf<=600 group by bj,fs有什么问题吗?
好像是派生的字段不能做分组键吧?如果是,可以这样
select count(*) t,bj,CAST(zf/10 as SIGNED) fs from mytable
where zf>=510 and zf<=600 group by bj, CAST(zf/10 as SIGNED)
<?php
include 'db.php';
$connection = mysql_connect($hostname, $dbusername, $dbpassword);
mysql_select_db($database);
mysql_query("SET NAMES UTF8");//$query = "SELECT bj,CAST(zf/10 as SIGNED) fs,zf from mytable WHERE zf>=510 AND zf<600";
$query = "select count(*) t,bj,CAST(zf/10 as SIGNED) fs from mytable where zf>=510 and zf<600 group by bj,fs";
$result = mysql_query($query);
while ($myrow = mysql_fetch_row($result)) {
echo $myrow[0]."-".$myrow[1]."-".$myrow[2]."<br>";//人数,班级,分数段
}
?>不知道有没有简单高效的办法把没有累计的人数 累计起来?
array("fs"=>51,"t"=>5),
array("fs"=>52,"t"=>5),
array("fs"=>53,"t"=>5),
);
$data2=$leiji=array();
foreach($data as $item){
$item[fs]=intval($item[fs]);//数据库的字段好像是字符串的,这个可以转一下.
$data2[$item[fs]]=intval($item[t]);
}
for($i=51;$i<=60;$i++){
$leiji[$i]=intval($leiji[$i-1])+$data2[$i];//50为0;
}//510-600的人数
print_r($leiji);
$data=array(
array("fs"=>51,"t"=>5),
array("fs"=>52,"t"=>5),
array("fs"=>53,"t"=>5),
);
$data2=$leiji=array();
foreach($data as $item){
$item[fs]=intval($item[fs]);//数据库的字段好像是字符串的,这个可以转一下.
$data2[$item[fs]]=intval($item[t]);
}
for($i=60;$i>=51;$i--){
$leiji[$i]=intval($leiji[$i+1])+$data2[$i];//61为0;
}//510-600的人数
print_r($leiji);
?>
{
$res = $db->query("select count(*) num from mytable where zf between 510 and ".($i+520));
$ret = $res->fetch();
echo $ret['num'];
}