php - MySQL 查询 – 有更好的方法吗？

Question

在我的博客上，我有一个功能可以计算每月发布的博客文章数量，这些可以显示在子菜单中。

它按年循环，然后按月另一个嵌套循环。每次运行此查询时都会计算每个月发布的文章数量。

这是查询:

Select * from DM_blog blog where  blog.id IN 
    ( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
    ( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type = 'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  and YEAR(blog.publish) = '".$year."' and MONTH(blog.publish) = '".$month."'"

有更好的方法吗？我可以一次执行所有月份的查询，而不必每年执行 12 次查询吗？完整代码在这里:

$this->benchmark->mark('blog_submenu_start');
    $dates = $this->blog_model->menu_dates($data);
    if($dates['years']){
        $i = 0;
        foreach($dates['years'] as $year){
            if($data['segments']['modal'] == $year['YEAR(blog.publish)']){
                $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['class'] = 'selected';
                $selected = true;
            }else if(date("Y") == $year['YEAR(blog.publish)'] && $selected == false){
                $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['class'] = 'selected';
                $selected = true;
            }

            // find the start month
            if($dates['first_entry'][0]['YEAR(blog.publish)'] == $year['YEAR(blog.publish)']){
                if($dates['first_entry'][0]['MONTH(blog.publish)'] < 2){
                    $limit_s['start'] = '1';
                    $limit_s['end'] = 13;
                }else{
                    $limit_s['start'] = $dates['first_entry'][0]['MONTH(blog.publish)'];
                    $limit_s['end'] = 12;
                }
            }else{
                $limit_s['start'] = 1;
                $limit_s['end'] = 13;
            }
            // run through the months
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_start');
            for($x=$limit_s['start'];$x<=$limit_s['end'];$x++){
                $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_start');
                $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_mysql_start');
                $count = $this->blog_model->month_count($year['YEAR(blog.publish)'],$x,$data);
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_mysql_end');
                if($last == false){
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_selected_start');
                    if($data['segments']['modal'] == $year['YEAR(blog.publish)'] and $data['segments']['para_one'] == $x){
                        $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['class'] = 'selected';
                    }
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_selected_end');
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_assign_start');
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['month'] = $x;
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['display_full'] = date('F',strtotime($year['YEAR(blog.publish)'].'-'.$x.'-01')); 
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['display_short'] = date('M',strtotime($year['YEAR(blog.publish)'].'-'.$x.'-01')); 
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['count'] = $count;
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['sef'] = $year['YEAR(blog.publish)'].'/'.$x.'/';
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_assign_end');
                    if(date("Y") == $year['YEAR(blog.publish)'] and date("m") == $x || date("n") == $x){
                        $last = true;
                    } // end date
                    $i++;
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_end');
                } // end last false
            } // end for
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_end');
        }
    }
    $this->benchmark->mark('blog_submenu_end');

$this->blog_model->month_count - 该函数如下:

function month_count($year,$month,$data=false){
    $query = $this->db->query("Select * from DM_blog blog where  blog.id IN 
    ( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
    ( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type = 'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  and YEAR(blog.publish) = '".$year."' and MONTH(blog.publish) = '".$month."'");
    return $query->num_rows(); 
}

Answer 1

您可以添加 group by 子句以按年份和月份对结果进行分组。

Select * from DM_blog blog where  blog.id IN 
( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type =     'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  GROUP BY     YEAR(blog.publish), MONTH(blog.publish)