MYSQL SELECT QUERY 显示之间的所有日期，即使数据不存在且 count=0

Question

Below is the query i have written to fetch the order count with status=1 for last week.

  SELECT count(*) as order_count,
         DATE_FORMAT(order_date,'%d-%b-%Y') as order_date,
         status 
    FROM customer_order 
   WHERE date(order_date) >= curdate() 
                              - INTERVAL DAYOFWEEK(curdate())+6 DAY
     AND date(order_date) < curdate() 
                              - INTERVAL DAYOFWEEK(curdate())-1 DAY 
     AND status=1
GROUP BY DATE_FORMAT(order_date, '%Y%m%d'),
         status

I am getting result only for the dates present in the table. I need all the dates with count = 0 if data is not present for particular date.

Answer 1

你可以尝试:

SELECT DATE_FORMAT(c1.order_date,'%d-%b-%Y') AS order_date
  FROM customer_order AS c1
 WHERE DATE_FORMAT(c1.order_date,'%d-%b-%Y') 
                  IN ( SELECT DATE_FORMAT(c2.order_date,'%d-%b-%Y') as order_date2
                         FROM customer_order AS c2
                        WHERE date(DATE_FORMAT(c2.order_date,'%d-%b-%Y')) >= curdate() 
                                - INTERVAL DAYOFWEEK(curdate())+6 DAY
                          AND date(DATE_FORMAT(c2.order_date,'%d-%b-%Y')) < curdate() 
                                - INTERVAL DAYOFWEEK(curdate())-1 DAY 
                          AND c2.status=1
                     GROUP BY DATE_FORMAT(DATE_FORMAT(c2.order_date,'%d-%b-%Y'), '%Y%m%d'),
                              c2.status
                       HAVING count(*)=0
                     )