时间：2021-07-01 10:21:17 帮助过：27人阅读
换其他语言比如C来处理统计汇总
代码呢。。。。
function foreverselect(){
$db = Db::getInstance();
//根据编码查询数据
$id=$_REQUEST['id']; //id
$datamonth=$_REQUEST['datamonth']; //日期
$ids=explode(',',$id); //截取
$jxtblist="";
$isok="yes"; //控制进度条标识
$datalist;//返回提示信息
$datalist1;
if(!empty($id)){
foreach($ids as $key=>$li){
$jxtbsql="select * from jx_tbprofile where id=".$li;
$jxtblist =$db-> query($jxtbsql);
foreach($jxtblist as $key=>$li1){
if($li1[execlevel]==0){ //0阶段
$list;
$success=0;//完成笔数
//先判断目的字段配置是否有误
$strtab="select * from jx_tbprofile_dtl where tableno='".$li1[tableno]."'"; //检索字段名称
$listtab=$db->query($strtab);
$strcol="show columns from ".$li1[desttable]; //获取目的表字段名称
$listcol=$db->query($strcol);
$ziduan;
$boole=false;
foreach ($listtab as $keytab=>$litab) {//配置目的表字段
foreach ($listcol as $keycol=>$licol)//目的表字段
{
if($litab[dest_column]==$licol['Field']){
$boole=true; //两个名称相互比较，如果遇到相同的给$boole赋值true。
//echo "相等";
break;
}else{
//echo $litab[dest_column];
//echo $licol['Field'];
$boole=false;
}
}
if(!$boole){//配置表中一个字段对应完之后获取$boole的值如果为false，就将改字段存入$ziduan函数中加入提示信息
$ziduan=$ziduan.$litab[dest_column];
$boole=false;
break;
}
}
if(!$boole){
$datalist=$datalist."未找到目的字段".$ziduan.",配置错误!";
}else{
//清除旧数据
$sql=str_replace("|datamonth|",$datamonth,$li1[sqldeldest]);//配置的删除语句
// echo $sql;
//mysql_query($sql) or die(mysql_error());
if (mysql_query($sql)){
//echo mysql_query($sql);
$datalist=$datalist."原数据表已清除!
";
$sql1=$li1[remarks];//配置的查询语句
$re=str_replace("|datamonth|",$datamonth,$sql1); //替换字符
$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error());
$jx = M ($li1[desttable]);
foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象
foreach ($listtab as $key=>$litab){
if(!empty($litab[mark])){//目的表中预设字段名称mark。
//echo $litab[mark];
$_POST[$litab[dest_column]]=$litab[mark];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}
//echo date("Y-m-d h:i:s")."
".$li1[tableprofile]."结算完毕!成功".$success."笔数据。
";
$datalist=$datalist.date("Y-m-d h:i:s")."
".$datamonth.$li1[tableprofile]."结算完毕!成功".$success."笔数据。
";
}else{
$datalist=$datalist."删除语句出问题!
";
}
}
$this->assign('isok',$isok);
}
}
}
}
}
你自己分析一下。是哪句代码执行过长，可以标示出来。
$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error());
$jx = M ($li1[desttable]);
foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象
foreach ($listtab as $key=>$litab){
if(!empty($litab[mark])){//目的表中预设字段名称mark。
//echo $litab[mark];
$_POST[$litab[dest_column]]=$litab[mark];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}
这段就是执行查询语句，然后将结果一条一条插入到目的表中。我在一张单独的表中配置了：来源表，目的表，查询语句，删除语句。在这段代码里找到配置的这条数据，然后执行查询表，插入新表的操作。其中有4条数据是量比较大的。我单独跑还挺快，但是4个连在一起跑就时间很长知道浏览器失效。
把最终生成的sql贴出来看看。表结构和索引情况如何，都描述清楚吧。
set_time_limit(0);？？？
把最终生成的sql贴出来看看。表结构和索引情况如何，都描述清楚吧。
在这3张表之间转换数据的jx_zy_detail_charge_belong，jx_zy_detail_charge_force,jx_zy_detail_charge
这是跑的第一条查询sql：jx_zy_detail_charge_belong->jx_zy_detail_charge_force
SELECT jx_zy_detail_charge_belong.data_month,
jx_zy_detail_charge_belong.inpatient_no,
jx_zy_detail_charge_belong.charge_date,
jx_zy_detail_charge_belong.op_id_code,
jx_zy_detail_charge_belong.charge_code,
jx_zy_detail_charge_belong.charge_fee,
jx_zy_detail_charge_belong.percentage,
jx_zy_detail_charge_belong.ward_code,
jx_zy_detail_charge_belong.dept_code,
jx_zy_detail_charge_belong.order_no,
jx_zy_detail_charge_belong.exec_unit,
jx_zy_detail_charge_belong.retprice,
jx_zy_detail_charge_belong.charge_amount,
jx_zy_detail_charge_belong.serial,
jx_zy_detail_charge_belong.group_no,
jx_zy_detail_charge_belong.bill_code,
jx_zy_detail_charge_belong.key_col,
jx_zy_detail_charge_belong.pat_name
FROM jx_zy_detail_charge_belong ,jx_mz_charge_item
WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and
jx_zy_detail_charge_belong.charge_code =jx_mz_charge_item.fee_code and
(jx_mz_charge_item.exec_unit is null or
jx_mz_charge_item.exec_unit =' ')
union all
SELECT jx_zy_detail_charge_belong.data_month,
jx_zy_detail_charge_belong.inpatient_no,
jx_zy_detail_charge_belong.charge_date,
jx_zy_detail_charge_belong.op_id_code,
jx_zy_detail_charge_belong.charge_code,
jx_zy_detail_charge_belong.charge_fee,
jx_zy_detail_charge_belong.percentage,
jx_zy_detail_charge_belong.ward_code,
jx_zy_detail_charge_belong.dept_code,
jx_zy_detail_charge_belong.order_no,
jx_mz_charge_item.exec_unit,
jx_zy_detail_charge_belong.retprice,
jx_zy_detail_charge_belong.charge_amount,
jx_zy_detail_charge_belong.serial,
jx_zy_detail_charge_belong.group_no,
jx_zy_detail_charge_belong.bill_code,
jx_zy_detail_charge_belong.key_col,
jx_zy_detail_charge_belong.pat_name
FROM jx_zy_detail_charge_belong ,jx_mz_charge_item
WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and
jx_zy_detail_charge_belong.charge_code= jx_mz_charge_item.fee_code and
jx_mz_charge_item.exec_unit is not null and
jx_mz_charge_item.exec_unit <>' '
就是从一个配置表，jx_mz_charge_item 中找到本表charge_code和fee_code相同，找到配置表的exec_unit符合条件就用这个值替换原表的科室值
第二条转换查询sql：jx_zy_detail_charge_force-》jx_zy_detail_charge
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit not in (select description from jx_code_ctl where item_no like 'ordept%') and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and not
( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or
jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and
( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or
jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit is null and data_month='|datamonth|'
将执行科室exec_unit字段在表jx_code_ctl中配置的科室，并且收费大类bill_code字段也在表jx_code_ctl中配置的大类中的数据的执行科室exec_unit改为开单科室dept_code
一共分为门诊和住院两组表，过程是一样的，这里是住院的流程说明，我单跑门诊和住院的第一跳sql，然后跑门诊和住院第二条sql，速度大概1个小时可以跑完，但是4条一起跑就要很久，而且页面也会挂掉。
后台执行产生所需数据，前台展示结果。
后台执行产生所需数据，前台展示结果。 是的，就是我把这4个数据量最大一起跑就很耗费时间，2个2个跑就快很多了。
用 EXPLAIN 指令检查你的 sql 指令，看看 mysql 会给你什么建议
用 EXPLAIN 指令检查你的 sql 指令，看看 mysql 会给你什么建议