我是另外个表查询出来海量数据 需要判断每条数据是否已还 但这样效率真的太低了怎么办 表结构是这样的
贴出完整代码 代码主要是统计图书借阅时长 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();
}

最佳答案

评论() 相关

收藏

赞

绿豆稀粥

ThinkPHP 是一个免费开源的,快速、简单的面向对象的 轻量级PHP开发框架 ,创立于2006年初,遵循Apache2开源协议发布,是为了敏捷WEB应用开发和简化企业应用开发而诞生的。ThinkPHP从诞生以来一直秉承简洁实用的设计原则,在保持出色的性能和至简的代码的同时,也注重易用性。并且拥有众多的原创功能和特性,在社区团队的积极参与下,在易用性、扩展性和性能方面不断优化和改进,已经成长为国内最领先和最具影响力的WEB应用开发框架,众多的典型案例确保可以稳定用于商业以及门户级的开发。