只是第1,2次选择。还要再做2次。后面基本一样重复第二次志愿的。100个学生运行数据大概花了6-7秒。求指导怎么可以优化下~~谢谢
if($_POST['cal'])
{
//--------------------------------------------第一志愿-------------------------------------------
$sql = "select * from hos;";
$res = mysql_query($sql)or die(mysql_error());
$info = mysql_fetch_array($res)or die(mysql_error());
//echo $info['total']; //得到总医院数
do{ //循环1
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线
if($info['same']==0) //如果不分男女时
{
$hos_id = $info['id']; //医院ID号
//echo $hos_id.",";
$hos_total = $info['total']; //医院总需求人数
//echo $hos_total.".";
$sql_total = "select count(*) as total from stu where first='".$hos_id."' and get=0 ;"; //添该志愿的学生数
$res_total = mysql_query($sql_total);
$info_total = mysql_fetch_array($res_total); //得到填报该志愿的学生数
if($hos_total > $info_total['total'])
{$sql1 = "select id from stu where first='".$hos_id."' and get=0 ORDER BY score DESC ;";}
else {$sql1 = "select id from stu where first='".$hos_id."' and get=0 ORDER BY score DESC limit $hos_total;";}
$res1 = mysql_query($sql1);
$info1 = mysql_fetch_array($res1);
do //循环2
{
$stu_id = $info1['id'];
$sql2 = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id."' ;";
$res2 = mysql_query($sql2);
}while($info1 = mysql_fetch_array($res1)); //循环2结束
}
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线 else //如果区分男女-----$info['same']==1;
{
$hos_id = $info['id']; //医院ID号
//男生SQL
$hos_total_men = $info['men']; //医院总需求男生数
$sql_total_men = "select count(*) as total_men from stu where first='".$hos_id."' and sex='f' and get=0 ;"; //添该志愿的男学生数
$res_total_men = mysql_query($sql_total_men);
$info_total_men = mysql_fetch_array($res_total_men); //得到填报该志愿的男学生数
if($hos_total_men > $info_total_men['total_men'])
{$sql1_men = "select id from stu where first='".$hos_id."' and sex='m' and get=0 ORDER BY score DESC ;";}
else {$sql1_men = "select id from stu where first='".$hos_id."'and sex='m' and get=0 ORDER BY score DESC limit $hos_total_men;";}
$res1_men = mysql_query($sql1_men);
$info1_men = mysql_fetch_array($res1_men);
do //循环2
{
//男生更新
$stu_id_men = $info1_men['id'];
$sql2_men = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id_men."' ;";
$res2_men = mysql_query($sql2_men);
}while($info1_men = mysql_fetch_array($res1_men));
//女生SQL
$hos_total_wom = $info['wom']; //医院总需求女生数
$sql_total_wom = "select count(*) as total_wom from stu where first='".$hos_id."' and sex='f' and get=0 ;"; //添该志愿的男学生数
$res_total_wom = mysql_query($sql_total_wom);
$info_total_wom = mysql_fetch_array($res_total_wom); //得到填报该志愿的女学生数
if($hos_total_wom > $info_total_wom['total_wom'])
{$sql1_wom = "select id from stu where first='".$hos_id."' and sex='f' and get=0 ORDER BY score DESC ;";}
else {$sql1_wom = "select id from stu where first='".$hos_id."' and sex='f' and get=0 ORDER BY score DESC limit $hos_total_wom;";}
$res1_wom = mysql_query($sql1_wom);
$info1_wom = mysql_fetch_array($res1_wom);
do //循环2
{
//女生更新
$stu_id_men = $info1_wom['id'];
$sql2_wom = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id_wom."';";
$res2_wom = mysql_query($sql2_wom);
}while($info1_wom = mysql_fetch_array($res1_wom));
//循环2结束
} }while($info = mysql_fetch_array($res)); //循环1结束
//--------------------------------------------第一志愿结束------------------------------------------- //----------------------------------------------第二志愿---------------------------------------------
$s_sql = "select * from hos;";
$s_res = mysql_query($s_sql)or die(mysql_error());
$s_info = mysql_fetch_array($s_res)or die(mysql_error());
do{ //循环1
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线
if($s_info['same']==0) //如果不分男女时
{
$s_hos_id = $s_info['id']; //医院ID号
$s_hos_total = $s_info['total']; //医院总需求人数
$s_sql_total = "select count(*) as total from stu where second='".$s_hos_id."' and get=0 ;"; //添该志愿的学生数
$s_res_total = mysql_query($s_sql_total);
$s_info_total = mysql_fetch_array($s_res_total); //得到第二志愿填报该志愿的学生数
$s_get_sql = "select count(*) as total from stu where result='".$s_hos_id."' ;"; //得到第一志愿已为HOS_ID的学生
$s_get_res = mysql_query($s_get_sql);
$s_get_info = mysql_fetch_array($s_get_res);
if($s_get_info['total'] == $s_hos_total){}
else
{
$xuqiu2 = $s_hos_toal-$s_get_info['total']; //还需要多少学生数
if( $xuqiu2 > $s_info_total['total']){$s_sql1 = "select id from stu where second='".$s_hos_id."' and get=0 ORDER BY score DESC ;";}
else {$s_sql1 = "select id from stu where second='".$s_hos_id."' and get=0 ORDER BY score DESC limit $xuqiu2;";}
$s_res1 = mysql_query($s_sql1);
$s_info1 = mysql_fetch_array($s_res1);
do //循环2
{
$s_stu_id = $s_info1['id'];
$s_sql2 = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id."' ;";
$s_res2 = mysql_query($s_sql2);
}while($s_info1 = mysql_fetch_array($s_res1));
}
}//循环2结束
else //如果区分男女-----$info['same']==1;
{
$s_hos_id = $s_info['id']; //医院ID号
//男生SQL
$s_hos_total_men = $s_info['men']; //医院总需求男生数
$s_sql_total_men = "select count(*) as total_men from stu where second='".$s_hos_id."' and sex='m' and get=0 ;"; //添该志愿的男学生数
$s_res_total_men = mysql_query($s_sql_total_men);
$s_info_total_men = mysql_fetch_array($s_res_total_men); //得到填报该志愿的男学生数 $s_get_sql_men = "select count(*) as total from stu where result='".$s_hos_id."' and sex='m' ;"; //得到第一志愿已为HOS_ID的男学生
$s_get_res_men = mysql_query($s_get_sql_men);
$s_get_info_men = mysql_fetch_array($s_get_res_men);
if($s_get_info_men['total'] == $s_hos_total_men){}
else
{
$xuqiu2_men = $s_hos_toal_men-$s_get_info_men['total']; //还需要多少男学生数
if($xuqiu2_men > $s_info_total_men['total_men'])
{$s_sql1_men = "select id from stu where second='".$s_hos_id."' and sex='m' and get=0 ORDER BY score DESC ;";}
else {$s_sql1_men = "select id from stu where second='".$s_hos_id."'and sex='m' and get=0 ORDER BY score DESC limit $xuqiu2_men;";}
$s_res1_men = mysql_query($s_sql1_men);
$s_info1_men = mysql_fetch_array($s_res1_men);
do //循环2
{
//男生更新
$s_stu_id_men = $s_info1_men['id'];
$s_sql2_men = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id_men."' ;";
$s_res2_men = mysql_query($s_sql2_men);
}while($s_info1_men = mysql_fetch_array($s_res1_men));
}
//女生SQL
$s_hos_total_wom = $s_info['wom']; //医院总需求女生数
$s_sql_total_wom = "select count(*) as total_wom from stu where second='".$s_hos_id."' and sex='f' and get=0 ;"; //添该志愿的女学生数
$s_res_total_wom = mysql_query($s_sql_total_wom);
$s_info_total_wom = mysql_fetch_array($s_res_total_wom); //得到填报该志愿的女学生数 $s_get_sql_wom = "select count(*) as total from stu where result='".$s_hos_id."' and sex='f' ;"; //得到第一志愿已为HOS_ID的女学生
$s_get_res_wom = mysql_query($s_get_sql_wom);
$s_get_info_wom = mysql_fetch_array($s_get_res_wom);
if($s_get_info_wom['total'] == $s_hos_total_wom){}
else
{
$xuqiu2_wom = $s_hos_toal_wom-$s_get_info_wom['total']; //还需要多少女学生数
if($xuqiu2_wom > $s_info_total_wom['total_wom'])
{$s_sql1_wom = "select id from stu where second='".$s_hos_id."' and sex='f' and get=0 ORDER BY score DESC ;";}
else {$s_sql1_wom = "select id from stu where second='".$s_hos_id."'and sex='f' and get=0 ORDER BY score DESC limit $xuqiu2_wom;";}
$s_res1_wom = mysql_query($s_sql1_wom);
$s_info1_wom = mysql_fetch_array($s_res1_wom);
do //循环2
{
//女生更新
$s_stu_id_wom = $s_info1_wom['id'];
$s_sql2_wom = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id_wom."' ;";
$s_res2_wom = mysql_query($s_sql2_wom);
}while($s_info1_wom = mysql_fetch_array($s_res1_wom));
} } }while($info = mysql_fetch_array($res));优化sql算法
if($_POST['cal'])
{
//--------------------------------------------第一志愿-------------------------------------------
$sql = "select * from hos;";
$res = mysql_query($sql)or die(mysql_error());
$info = mysql_fetch_array($res)or die(mysql_error());
//echo $info['total']; //得到总医院数
do{ //循环1
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线
if($info['same']==0) //如果不分男女时
{
$hos_id = $info['id']; //医院ID号
//echo $hos_id.",";
$hos_total = $info['total']; //医院总需求人数
//echo $hos_total.".";
$sql_total = "select count(*) as total from stu where first='".$hos_id."' and get=0 ;"; //添该志愿的学生数
$res_total = mysql_query($sql_total);
$info_total = mysql_fetch_array($res_total); //得到填报该志愿的学生数
if($hos_total > $info_total['total'])
{$sql1 = "select id from stu where first='".$hos_id."' and get=0 ORDER BY score DESC ;";}
else {$sql1 = "select id from stu where first='".$hos_id."' and get=0 ORDER BY score DESC limit $hos_total;";}
$res1 = mysql_query($sql1);
$info1 = mysql_fetch_array($res1);
do //循环2
{
$stu_id = $info1['id'];
$sql2 = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id."' ;";
$res2 = mysql_query($sql2);
}while($info1 = mysql_fetch_array($res1)); //循环2结束
}
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线 else //如果区分男女-----$info['same']==1;
{
$hos_id = $info['id']; //医院ID号
//男生SQL
$hos_total_men = $info['men']; //医院总需求男生数
$sql_total_men = "select count(*) as total_men from stu where first='".$hos_id."' and sex='f' and get=0 ;"; //添该志愿的男学生数
$res_total_men = mysql_query($sql_total_men);
$info_total_men = mysql_fetch_array($res_total_men); //得到填报该志愿的男学生数
if($hos_total_men > $info_total_men['total_men'])
{$sql1_men = "select id from stu where first='".$hos_id."' and sex='m' and get=0 ORDER BY score DESC ;";}
else {$sql1_men = "select id from stu where first='".$hos_id."'and sex='m' and get=0 ORDER BY score DESC limit $hos_total_men;";}
$res1_men = mysql_query($sql1_men);
$info1_men = mysql_fetch_array($res1_men);
do //循环2
{
//男生更新
$stu_id_men = $info1_men['id'];
$sql2_men = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id_men."' ;";
$res2_men = mysql_query($sql2_men);
}while($info1_men = mysql_fetch_array($res1_men));
//女生SQL
$hos_total_wom = $info['wom']; //医院总需求女生数
$sql_total_wom = "select count(*) as total_wom from stu where first='".$hos_id."' and sex='f' and get=0 ;"; //添该志愿的男学生数
$res_total_wom = mysql_query($sql_total_wom);
$info_total_wom = mysql_fetch_array($res_total_wom); //得到填报该志愿的女学生数
if($hos_total_wom > $info_total_wom['total_wom'])
{$sql1_wom = "select id from stu where first='".$hos_id."' and sex='f' and get=0 ORDER BY score DESC ;";}
else {$sql1_wom = "select id from stu where first='".$hos_id."' and sex='f' and get=0 ORDER BY score DESC limit $hos_total_wom;";}
$res1_wom = mysql_query($sql1_wom);
$info1_wom = mysql_fetch_array($res1_wom);
do //循环2
{
//女生更新
$stu_id_men = $info1_wom['id'];
$sql2_wom = "UPDATE stu SET result='".$hos_id."',get=1 where id='".$stu_id_wom."';";
$res2_wom = mysql_query($sql2_wom);
}while($info1_wom = mysql_fetch_array($res1_wom));
//循环2结束
} }while($info = mysql_fetch_array($res)); //循环1结束
//--------------------------------------------第一志愿结束------------------------------------------- //----------------------------------------------第二志愿---------------------------------------------
$s_sql = "select * from hos;";
$s_res = mysql_query($s_sql)or die(mysql_error());
$s_info = mysql_fetch_array($s_res)or die(mysql_error());
do{ //循环1
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//分界线
if($s_info['same']==0) //如果不分男女时
{
$s_hos_id = $s_info['id']; //医院ID号
$s_hos_total = $s_info['total']; //医院总需求人数
$s_sql_total = "select count(*) as total from stu where second='".$s_hos_id."' and get=0 ;"; //添该志愿的学生数
$s_res_total = mysql_query($s_sql_total);
$s_info_total = mysql_fetch_array($s_res_total); //得到第二志愿填报该志愿的学生数
$s_get_sql = "select count(*) as total from stu where result='".$s_hos_id."' ;"; //得到第一志愿已为HOS_ID的学生
$s_get_res = mysql_query($s_get_sql);
$s_get_info = mysql_fetch_array($s_get_res);
if($s_get_info['total'] == $s_hos_total){}
else
{
$xuqiu2 = $s_hos_toal-$s_get_info['total']; //还需要多少学生数
if( $xuqiu2 > $s_info_total['total']){$s_sql1 = "select id from stu where second='".$s_hos_id."' and get=0 ORDER BY score DESC ;";}
else {$s_sql1 = "select id from stu where second='".$s_hos_id."' and get=0 ORDER BY score DESC limit $xuqiu2;";}
$s_res1 = mysql_query($s_sql1);
$s_info1 = mysql_fetch_array($s_res1);
do //循环2
{
$s_stu_id = $s_info1['id'];
$s_sql2 = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id."' ;";
$s_res2 = mysql_query($s_sql2);
}while($s_info1 = mysql_fetch_array($s_res1));
}
}//循环2结束
else //如果区分男女-----$info['same']==1;
{
$s_hos_id = $s_info['id']; //医院ID号
//男生SQL
$s_hos_total_men = $s_info['men']; //医院总需求男生数
$s_sql_total_men = "select count(*) as total_men from stu where second='".$s_hos_id."' and sex='m' and get=0 ;"; //添该志愿的男学生数
$s_res_total_men = mysql_query($s_sql_total_men);
$s_info_total_men = mysql_fetch_array($s_res_total_men); //得到填报该志愿的男学生数 $s_get_sql_men = "select count(*) as total from stu where result='".$s_hos_id."' and sex='m' ;"; //得到第一志愿已为HOS_ID的男学生
$s_get_res_men = mysql_query($s_get_sql_men);
$s_get_info_men = mysql_fetch_array($s_get_res_men);
if($s_get_info_men['total'] == $s_hos_total_men){}
else
{
$xuqiu2_men = $s_hos_toal_men-$s_get_info_men['total']; //还需要多少男学生数
if($xuqiu2_men > $s_info_total_men['total_men'])
{$s_sql1_men = "select id from stu where second='".$s_hos_id."' and sex='m' and get=0 ORDER BY score DESC ;";}
else {$s_sql1_men = "select id from stu where second='".$s_hos_id."'and sex='m' and get=0 ORDER BY score DESC limit $xuqiu2_men;";}
$s_res1_men = mysql_query($s_sql1_men);
$s_info1_men = mysql_fetch_array($s_res1_men);
do //循环2
{
//男生更新
$s_stu_id_men = $s_info1_men['id'];
$s_sql2_men = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id_men."' ;";
$s_res2_men = mysql_query($s_sql2_men);
}while($s_info1_men = mysql_fetch_array($s_res1_men));
}
//女生SQL
$s_hos_total_wom = $s_info['wom']; //医院总需求女生数
$s_sql_total_wom = "select count(*) as total_wom from stu where second='".$s_hos_id."' and sex='f' and get=0 ;"; //添该志愿的女学生数
$s_res_total_wom = mysql_query($s_sql_total_wom);
$s_info_total_wom = mysql_fetch_array($s_res_total_wom); //得到填报该志愿的女学生数 $s_get_sql_wom = "select count(*) as total from stu where result='".$s_hos_id."' and sex='f' ;"; //得到第一志愿已为HOS_ID的女学生
$s_get_res_wom = mysql_query($s_get_sql_wom);
$s_get_info_wom = mysql_fetch_array($s_get_res_wom);
if($s_get_info_wom['total'] == $s_hos_total_wom){}
else
{
$xuqiu2_wom = $s_hos_toal_wom-$s_get_info_wom['total']; //还需要多少女学生数
if($xuqiu2_wom > $s_info_total_wom['total_wom'])
{$s_sql1_wom = "select id from stu where second='".$s_hos_id."' and sex='f' and get=0 ORDER BY score DESC ;";}
else {$s_sql1_wom = "select id from stu where second='".$s_hos_id."'and sex='f' and get=0 ORDER BY score DESC limit $xuqiu2_wom;";}
$s_res1_wom = mysql_query($s_sql1_wom);
$s_info1_wom = mysql_fetch_array($s_res1_wom);
do //循环2
{
//女生更新
$s_stu_id_wom = $s_info1_wom['id'];
$s_sql2_wom = "UPDATE stu SET result='".$s_hos_id."',get=1 where id='".$s_stu_id_wom."' ;";
$s_res2_wom = mysql_query($s_sql2_wom);
}while($s_info1_wom = mysql_fetch_array($s_res1_wom));
} } }while($info = mysql_fetch_array($res));优化sql算法
大致这样吧当然 要按你要求去改
我不知道 我说到你的点子上去么 毕竟不晓得你的意图
改了后 绝对是质的飞跃
for($i=1;$i<=3;$i++)
{
//开始取医院信息
$sql = "select * from hos;";
$res = mysql_query($sql)or die(mysql_error());
$info = mysql_fetch_array($res)or die(mysql_error());
while($info = mysql_fetch_array($res))
{
$id=$info['id']; //得到医院ID
$total=$info['total']; //得到医院需求总人数
if($i==1){$key='first';}
else if($i==2){$key='second';}
else {$key='third';}
if($info['same']==0) //不区分男女时
{
$sql = "update stu set result=$id,get=1 where id in (select id ORDER BY score DESC) and $key=$id and get=0 ";
$num_sql = "select count(id) as total from stu where result=$id ;"; //得到第i志愿已为医院_ID的学生
$num_res = mysql_query($num_sql);
$num_info = mysql_fetch_array($num_res);
if($num_info['total']>$total)
{
$sql.="limit $total ";
mysql_query($sql);
}
else if($num_info['total']==$total){ $sql.="limit 0 "; mysql_query($sql);}
else
{
$need=$total-$num_info['total'];
$sql.="limit $need";
mysql_query($sql);
}
}
else //区分男女
{
$m_total=$info['men']; //得到医院需求总男生数
$f_total=$info['wom']; //得到医院需求总女生数
$sql = "update stu set result=$id,get=1 where id in (select id ORDER BY score DESC) and $key=$id and get=0 ";
$num_msql = "select count(id) as total from stu where result=$id and sex='m'";
$num_mres = mysql_query($num_msql);
$num_minfo = mysql_fetch_array($num_mres);
if($num_minfo['total']>$info['men']){$sql.="limit $m_total "; mysql_query($sql);}
else if($num_minfo['total']==$info['men']){$sql.="limit 0 "; mysql_query($sql);}
else
{
$m_need=$info['men']-$num_minfo['total'];
$sql.="and sex='m' limit $m_need ";
mysql_query($sql);
}
$sql1 = "update stu set result=$id,get=1 where id in (select id ORDER BY score DESC) and $key=$id and get=0 ";
$num_wsql = "select count(id) as total from stu where result=$id and sex='f'";
$num_wres = mysql_query($num_wsql);
$num_winfo = mysql_fetch_array($num_wres);
if($num_winfo['total']>$info['wom']){$sql1.="limit $f_total " ;mysql_query($sql);}
else if($num_winfo['total']==$info['wom']){$sql1.="limit 0 "; mysql_query($sql);}
else
{
$w_need=$info['wom']-$num_minfo['total'];
$sql1.="and sex='f' limit '$w_need' ";
mysql_query($sql1);
}
}
}
}
}
//----------------------------------------------最后随即分配---------------------------------------------
$l_sql = "select * from hos;";
$l_res = mysql_query($l_sql)or die(mysql_error());
$l_info = mysql_fetch_array($l_res)or die(mysql_error());
do{ //循环1 $l_id = $l_info['id']; //医院ID号
$l_total = $l_info['total']; //医院总需求人数
$l_get_sql = "select count(*) as total from stu where result='".$l_id."';";
$l_get_res = mysql_query($l_get_sql);
$l_get_info = mysql_fetch_array($l_get_res); //得到结果已为医院ID的学生数
if($l_get_info['total'] == $l_total){}
else
{
$xuqiu4 = $l_total-$l_get_info['total']; //还需要多少学生数
$sql = "update stu set result='$l_id',get=1 where id in(select id ORDER BY score DESC ) and get=0 limit $xuqiu4 ";
mysql_query($sql);
}
}while($l_info = mysql_fetch_array($l_res));
// 录取所有学生
function fetchAllStu()
{
// 志愿批次列表
$batch_list = array("first", "second", "third"); // 涉及多个更新操作,需要事务处理
mysql_query("start transaction"); $sql = "select * from hos";
$res = mysql_query($sql) or die(mysql_error());
while($info = mysql_fetch_array($res))
{
foreach ($batch_list as $val)
{
if(empty($info['same']))
{
// 不分男女录取
$need_cnt = $info['total'] - getStuCount($info['id']);
if(!fetchHosStu($val, $info['id'], $need_cnt))
{
mysql_query("rollback");
return false;
}
}
else
{
// 录取男生
$m_need_cnt = $info['men'] - getStuCount($info['id'], 'm');
if(!fetchHosStu($val, $info['id'], $m_need_cnt, 'm'))
{
mysql_query("rollback");
return false;
}
// 录取女生
$f_need_cnt = $info['wom'] - getStuCount($info['id'], 'f');
if(!fetchHosStu($val, $info['id'], $f_need_cnt, 'f'))
{
mysql_query("rollback");
return false;
}
}
}
}
mysql_query("commit");
return true;
}// 录取指定医院的学生
function fetchHosStu($index, $hos_id, $num_limit, $sex=null)
{
global $field_list;
$sub_condition = '';
if (isset($sex))
{
if(!isValidSex($sex))
{
return false;
}
$sub_condition = "where sex='{$sex}'";
} // 只有人没录取满才能继续录取
if($num_limit > 0)
{
$cond = array();
$cond[] = "id in (select id from stu {$sub_condition} order by score desc limit {$num_limit}) and get=0";
$cond[] = "{$index}={$hos_id}";
// 拼接SQL
$condition = "where ". implode(' and ', $cond);
$sql = "update stu set result={$hos_id},get=1 where {$condition}";
//echo $sql;
return mysql_query($sql);
}
return false;
}// 判断是否是合法的性别
function isValidSex($sex)
{
return in_array(strtolower($sex), array('f', 'm'));
}// 取得医院已录取学生数
function getStuCount($hos_id, $sex=null)
{
$cond = array();
if (isset($sex))
{
if(!isValidSex($sex))
{
return false;
}
$cond[] = "sex='{$sex}'";
}
$cond[] = "result={$hos_id}";
// 拼接SQL
$condition = "where ". implode(' and ', $cond);
$sql = "select count(1) as total from stu {$condition}";
//echo $sql;
$res = mysql_query($sql);
$info = mysql_fetch_array($res);
return $info['total'];
}fetchAllStu();
2.这里假设楼主是需要按批次来进行更新的,如果没有这个限制,还可以进一步优化。
3.53行没用了,请删除之
4.对楼主提点建议。请将代码以代码格式发布,文本格式真的让人很抓狂。雷同的代码尽量封装成函数,咱们是程序员,不是编辑。函数体的代码尽量控制在100行以内,编写和调试都很简单,百万字的长篇小说都会有段落的。
{
// 调用放这里
}
select count(1) as total from stu where sex='m' and result=1
update stu set result=1,get=1 where id in (select id from stu where sex='m' order by score desc limit 4) and get=0 and first=1
还是没结果~不进行任何更新呢~
你是楼主吗?还是马甲?
自己有问题就自己发帖,在人家的帖子里捣什么乱
$conn = mysql_connect("localhost", "test", "test");
mysql_select_db("test", $conn);
mysql_set_charset("utf8", $conn);
// 录取所有学生
function fetchAllStu()
{
// 志愿批次列表
$batch_list = array("first", "second", "third"); // 涉及多个更新操作,需要事务处理
mysql_query("start transaction"); $sql = "select * from hos";
$res = mysql_query($sql) or die(mysql_error());
while($info = mysql_fetch_array($res))
{
foreach ($batch_list as $val)
{
if(empty($info['same']))
{
// 不分男女录取
$need_cnt = $info['total'] - getStuCount($info['id']);
if(!fetchHosStu($val, $info['id'], $need_cnt))
{
mysql_query("rollback");
return false;
}
}
else
{
// 录取男生
$m_need_cnt = $info['men'] - getStuCount($info['id'], 'm');
if(!fetchHosStu($val, $info['id'], $m_need_cnt, 'm'))
{
mysql_query("rollback");
return false;
} // 录取女生
$f_need_cnt = $info['wom'] - getStuCount($info['id'], 'f');
if(!fetchHosStu($val, $info['id'], $f_need_cnt, 'f'))
{
mysql_query("rollback");
return false;
}
}
}
}
mysql_query("commit");
return true;
}// 录取指定医院的学生
function fetchHosStu($index, $hos_id, $num_limit, $sex=null)
{
$sub_condition = '';
if (isset($sex))
{
if(!isValidSex($sex))
{
return false;
}
$sub_condition = "where sex='{$sex}'";
} // 只有人没录取满才能继续录取
if($num_limit > 0)
{
$cond = array();
$cond[] = "{$index}={$hos_id}"; // 拼接SQL
$condition = implode(' and ', $cond);
$sql = "update stu set result=1,get=1 where get=0 and {$condition} order by score desc limit {$num_limit}";
//echo $sql;
return mysql_query($sql);
}
return false;
}// 判断是否是合法的性别
function isValidSex($sex)
{
return in_array(strtolower($sex), array('f', 'm'));
}// 取得医院已录取学生数
function getStuCount($hos_id, $sex=null)
{
$cond = array();
if (isset($sex))
{
if(!isValidSex($sex))
{
return false;
}
$cond[] = "sex='{$sex}'";
}
$cond[] = "result={$hos_id}"; // 拼接SQL
$condition = "where ". implode(' and ', $cond);
$sql = "select count(1) as total from stu {$condition} for update";
//echo $sql;
$res = mysql_query($sql);
$info = mysql_fetch_array($res);
return $info['total'];
}fetchAllStu();
1.有一个bug修复了,在getStuCount函数中,select语句需要锁记录,以免发生数量变更。
2.更新语句去掉子查询,直接用score排序即可。以上程序在我本地调试通过,10条记录耗时约0.02秒,应该比楼主之前的效率要高很多了。