分表分页问题

傷城~ 2023-10-07 09:02 7阅读 0赞

( 此文章只做自己工作记录使用）

假如每天一个表表的名称定义格式为：sys\_client\_20200106

1、确定要分页的数据总表数(table\_count)

sys\_get\_timespan（为把日期差换算为天数的函数）

$day_count=sys_get_timespan($start_date,$end_date,'d');
    $day_count=$day_count+1;

2、循环查出没个表满足条件的数量、limit的起始位置(table\_index)和结束位置(table\_end)并统计所有满足条件的数据数量total\_count

$total_count=0;
                for ($i=0;$i<$day_count;$i++)
                {
                    $temp_date=date("Ymd",strtotime('-'.$i.' day',strtotime($end_date)));
                    $table_name="sys_client_".$temp_date;
    
                    $sql_suffix=" from $table_name where 1=1 ";
                    if ($regdate_start) $sql_suffix .= "and regdate>='$regdate_start' ";
                    if ($regdate_end) $sql_suffix .= "and regdate<='$regdate_end' ";
                
    
                    $sqlstr="select count(id) $sql_suffix";
                    //echo $sqlstr."<br>";
                    $table_count=$this->get_one_bysql($sqlstr);
                    if ($table_count>0)
                    {
                        $total_count+=$table_count;
                        $table_sub_arr[$table_sub_j]['name']=$table_name;
                        $table_sub_arr[$table_sub_j]['count']=$table_count;
                        if ($table_sub_j==0) {
                            $table_sub_arr[$table_sub_j]['start_index']=0;
                            $table_sub_arr[$table_sub_j]['end_index']=$table_count-1;
                        }
                        else
                        {
                           
                            $table_sub_arr[$table_sub_j]['start_index']=$total_count - $table_count;
                            $table_sub_arr[$table_sub_j]['end_index']=$total_count-1;
                        }
                        $table_sub_j++;
                    }
                }

3、确定请求分页数据请求需要查询的表的开始位置和需要查询的limit数据条数确定查询每个表的sql语句，用union连接组合每个查询语句即可

$query_table_arr=[];
                $j=0;
                for ($i=0;$i<count($table_sub_arr);$i++)
                {
                    if ($start>=$table_sub_arr[$i]['start_index'] && $end<=$table_sub_arr[$i]['end_index'])
                    {
                        $query_table_arr[$j]['name']=$table_sub_arr[$i]['name'];
    
                        $query_table_arr[$j]['start_index']=$start-$table_sub_arr[$i]['start_index'];
                        $query_table_arr[$j]['limitcount']=$page_count;
    
                        break;
                    }
    
                    if ($start>=$table_sub_arr[$i]['start_index'] && $end>=$table_sub_arr[$i]['end_index'])
                    {
                        $query_table_arr[$j]['name'] = $table_sub_arr[$i]['name'];
                        $query_table_arr[$j]['start_index'] = $start - $table_sub_arr[$i]['start_index'];
                        $query_table_arr[$j]['limitcount'] = $page_count;
                        $j++;
                        continue;
                    }
    
                    if ($start<$table_sub_arr[$i]['start_index'] && $end>$table_sub_arr[$i]['end_index'])
                    {
                        $query_table_arr[$j]['name'] = $table_sub_arr[$i]['name'];
                        $query_table_arr[$j]['start_index'] = 0;
                        $query_table_arr[$j]['limitcount'] = $page_count;
                        $j++;
                        continue;
                    }
    
                    if ($start<$table_sub_arr[$i]['start_index'] && $end<$table_sub_arr[$i]['end_index'] && $end>$table_sub_arr[$i]['start_index'])
                    {
                        $query_table_arr[$j]['name'] = $table_sub_arr[$i]['name'];
                        $query_table_arr[$j]['start_index'] = 0;
                        $query_table_arr[$j]['limitcount'] = $end - $table_sub_arr[$i]['start_index'] + 1;
    
                        $j++;
                        continue;
                    }
                }
                //dump($query_table_arr);
                $source_sqlstr = "";
                for ($i=0;$i<count($query_table_arr);$i++)
                {
                    $table_name=$query_table_arr[$i]['name'];
    
                    if ($table_name) {
                        $sql_suffix=" from $table_name where 1=1 ";
                        if ($regdate_start) $sql_suffix .= "and regdate>='$regdate_start' ";
                        if ($regdate_end) $sql_suffix .= "and regdate<='$regdate_end' ";
                        
    
                        $start=$query_table_arr[$i]['start_index'];
    
                        $source_sqlstr .= "(";
                        if (!$export)
                            $source_sqlstr .= "select * $sql_suffix order by regdate desc limit $start,".$query_table_arr[$i]['limitcount'];
                        else $source_sqlstr .= "select * $sql_suffix order by regdate desc";
    
                        $source_sqlstr .= ") UNION";
                    }
    
                }
    
                $source_sqlstr=substr($source_sqlstr,0,-5);

打印：dump($query\_table\_arr);

array(2) {
      [0] => array(3) {
        ["name"] => string(19) "sys_client_20200206"
        ["start_index"] => int(1090)
        ["limitcount"] => int(10)
      }
      [1] => array(3) {
        ["name"] => string(19) "sys_client_20200205"
        ["start_index"] => int(717)
        ["limitcount"] => int(10)
      }
    }

打印$source\_sqlstr如下：

(select * from sys_client_20200206 where 1=1 and regdate>='2020-01-04 00:00:00' and regdate<='2020-02-06 15:23:53' and controller_id=21 order by regdate desc limit 1090,10) UNION(select * from sys_client_20200205 where 1=1 and regdate>='2020-01-04 00:00:00' and regdate<='2020-02-06 15:23:53' and controller_id=21 order by regdate desc limit 717,10)