<pre name="code" class="php"> /**
*获得一段时间内用户订单量(支持分表)
*/
public function getOrderNumByUserIdWithTime($useId, $start, $end) {
$result = 0;
$year_now = date('Y');//当前年份
$year_start = date('Y', strtotime($start)); //开始年份
$year_end = date('Y', strtotime($end)); //结束年份
if((strtotime($end) - strtotime($start)) >= 0) { //查询
for($i = $year_start; $i <= $year_end; $i++) { //循环开始时间和结束时间的年数
//计算开始时间
if($i == $year_start) {
$tmp_start = $start;
} else {
$tmp_start = $i.'-01-01 00:00:00';
}
//计算结束时间
if($i == $year_end) {
$tmp_end = $end;
} else {
$tmp_end = $i .'-12-31 23:59:59';
}
//定位分表的表
if($i == $year_now) {
$t_order_table = 't_order';
} else {
$t_order_table = 't_order_' . $i;
}
//查询数据库
$sql = "select count(1) as num from " . $t_order_table ." where user_id=:user_id and created >= UNIX_TIMESTAMP(:start_time) and created <= UNIX_TIMESTAMP(:end_time) and status in(1,4)";
$command = Order::getDbReadonlyConnection()->createCommand($sql);
$command->bindParam(':user_id', $userId);
$command->bindParam(':start_time', $tmp_start);
$command->bindParam(':end_time', $tmp_end);
//$count = $command->queryScalar();
$data = $command->queryRow();
$result = $result + isset($data['num']) ? $data['num'] : 0;
}
}
return $result;
}
PHP数据库分表查询
最新推荐文章于 2024-07-13 18:20:35 发布