php - 将 2 个相似的分组 MySql 查询(同一个表)组合在一个并循环中以有效的方式获取数组

Question

//last month            
$sqlQuery = "SELECT status, COUNT( id ) AS how_many, status_color
                        FROM table
                        JOIN statuses
                        USING ( status ) 
                        WHERE office_id = ".$office_id."
                        AND wdate
                        BETWEEN DATE_FORMAT( CONCAT(YEAR(CURDATE() - INTERVAL 1 MONTH), '-' ,MONTH(CURDATE())-1, '-01'),  '%Y-%m-%d 00:00:00' ) 
                        AND DATE_FORMAT( LAST_DAY(CURDATE()-interval 1 month),'%Y-%m-%d 23:59:59' ) 
                        GROUP BY status, status_color";

//this month                    
$sqlQuery = "SELECT status, COUNT( id ) AS how_many, status_color
                        FROM table
                        JOIN statuses
                        USING ( status )
                        WHERE office_id = ".$office_id."
                        AND wdate
                        BETWEEN DATE_FORMAT( CONCAT(YEAR(CURDATE()), '-' ,MONTH(CURDATE()), '-01'),  '%Y-%m-%d 00:00:00' ) 
                        AND NOW() 
                        GROUP BY status, status_color"; 

一个查询的循环很简单

$result = mysql_query($sqlQuery) or die('Error, query failed: '.mysql_error());
$num_rows = mysql_num_rows($result);
$i = 1; 

while($row = mysql_fetch_assoc($result)){
    $this_month[$i] = array("label" => $row['status'], "fillColor" => $row['status_color'], "data" => array($row['how_many']));
    $i++;
}

如何组合上面的查询并使结果像这样的一个数组

$result = mysql_query($sqlQuery) or die('Error, query failed: '.mysql_error());
$num_rows = mysql_num_rows($result);
$i = 1; 

while($row = mysql_fetch_assoc($result)){
    $both_months[$i] = array("label" => $row['status'], "fillColor" => $row['status_color'], "data" => array($row['how_many1']), $row['how_many2']));
    $i++;
}

请注意，任何查询都可以根据 waybill_status 列返回零个、一个或最多 4 个组

感谢您的帮助。

Answer 1

SELECT status, 
       SUM( wdate 
          BETWEEN DATE_FORMAT( CONCAT(YEAR(CURDATE() - INTERVAL 1 MONTH), '-' ,MONTH(CURDATE())-1, '-01'),  '%Y-%m-%d 00:00:00' ) 
            AND DATE_FORMAT( LAST_DAY(CURDATE()-interval 1 month),'%Y-%m-%d 23:59:59' ) ) how_many_1,
       SUM( wdate
          BETWEEN DATE_FORMAT( CONCAT(YEAR(CURDATE()), '-' ,MONTH(CURDATE()), '-01'),  '%Y-%m-%d 00:00:00' ) 
            AND NOW() ) how_many_2,
       status_color
FROM table
JOIN statuses
USING ( status ) 
WHERE office_id = ".$office_id."
GROUP BY status, status_color