我是另外个表查询出来海量数据 需要判断每条数据是否已还 但这样效率真的太低了怎么办 表结构是这样的
贴出完整代码 代码主要是统计图书借阅时长
public function jysc()
{
$act = input('act');
if($act == 'get_bottow_nian_top'){
$nian = input('nian');
if($nian==""){
$nian = date("Y",time());
}
$first=$nian."-01-01 00:00:00";
$end=$nian."-12-31 23:59:59";
$re = Db::name('loan')
->alias('loanLog')
->join('book book','book.bookrecno=loanLog.bookrecno','LEFT')
->where("loanLog.logType=30001 and loanLog.regTimeInStr BETWEEN '".$first."' and '".$end."'")
->group('loanLog.bookrecno')
->field('*,loanLog.bookrecno as recno')
->select();
//查询还书时间
$sql ="CREATE TEMPORARY TABLE tmp_table(bookrecno int(11) NOT NULL,rdid VARCHAR(10) NOT NULL,title VARCHAR(10) NOT NULL,author VARCHAR(255) NOT NULL,publisher VARCHAR(255),jysc int(11) NOT NULL)";
Db::execute($sql);
$model = Db::name('loan');
$sqlstr ="";
foreach($re as $key =>$value)
{
$recno = $value['recno'];
$rdid = $value['rdid'];//读者ID
$regTimeInStr = $value['regTimeInStr'];//借阅时间
//判断该书是否已还
$one = $model-> where("rdid='$rdid' and logType=30002 and bookrecno=$recno and regTimeInStr>='$regTimeInStr'")->field('regTimeInStr')->find();
if(!$one){//没有还书记录
unset($re[$key]);
}else{
//$re[$key]['yqzt'] = '<span class="label label-info">已还</span>';
$re[$key]['hssj'] = $one['regTimeInStr'];
$hssj = strtotime($one['regTimeInStr']);
$time = time();
$bigen = strtotime($regTimeInStr);//开始时间 时间戳
$over = time();//结束时间 时间戳
$cle = $hssj - $bigen; //得出时间戳差值
$d = floor($cle/3600/24);
$re[$key]['d'] =$d;
//开始创建临时表
$title=$value['title'];
$author = $value['author'];
$publisher=$value['publisher'];
$sqlstr=$sqlstr+"(".$recno.",'".$rdid."','".$title."','".$author."','".$publisher."',".$d."),";
}
}
$sql = rtrim($sqlstr, ',');
$sql ="INSERT INTO tmp_table (bookrecno,rdid,title,author,publisher,jysc)VALUES $sql";
Db::execute($sql);
$a = Db::query("select *,sum(jysc) as d from tmp_table GROUP BY bookrecno order by jysc desc limit 10");
Db::execute("DROP TABLE tmp_table");//释放临时表
//->buildSql();
exit(json_encode( array("data" => $a)));
}
return $this->fetch();
} 最佳答案
