php - mysql 按周分组两年

Question

您好，我正在编写一个查询来获取面积图的结果。我有两年的数据。我使用 mysql 按日期分组来获取每日结果，但是当我使用按周分组时，我无法找到一种方法来分隔两年的每周数据，因为它返回的周数为 4,5

我的代码是

     $results=mysql_query("SELECT SUM(rate) as rate, week(rdate) as rdate 
    FROM cpustats 
    WHERE server_id='$server_id' 
    GROUP BY week(rdate)") or die(mysql_error());
    $data3='';
    $data4='';
    while ($row = mysql_fetch_array($results)) {
        //one day has lots of records i devide that for get average
       $rk=mysql_query("SELECT rate as rdate FROM cpustats 
    WHERE server_id='$server_id' 
    AND week(rdate)='".$row['rdate']."'") or die(mysql_error());
       $nr=mysql_num_rows($rk);
       //here has issue which shows sum of two years same week data
       $usage=round($row['rate']/$nr,2);

       //tfunction return start and end of week by passing week number
       $week=getStartAndEndDate($row['rdate']);

       $data3[] = "'".$week."'";
       $data4[] = "$usage";

}

谁能帮我获取两年内单独的每周数据

Answer 1

您还需要添加按年份分组，以便按周和年份对结果进行分组

SELECT 
SUM(rate) as rate,
WEEK(rdate) as rdate ,
YEAR(rdate) as rdate_year
FROM cpustats 
WHERE server_id='$server_id' 
GROUP BY YEAR(rdate),WEEK(rdate)