如果同一天的记录不存在，则获取 0

Question

我要做的是获得数据分析，这是工作良好，除了一件事只有，我得到日期的总记录，以前的总记录的同一天和特定的一天的总记录，这是工作绝对良好。

但唯一的问题是我想要那些天的数据也没有行

例如:我有9月1日、9月2日、9月3日和9月6日的数据，但我想要4日的结果;9月5日，总比分为0。

SELECT (SELECT COUNT(ActivityID) FROM Activity WHERE DATE(CreatedDate) BETWEEN '2015-08-31' AND '2015-09-07'
) as total_post, (SELECT COUNT(ActivityID) FROM Activity WHERE DATE(CreatedDate) BETWEEN '2015-08-23'
 AND '2015-08-30') as previous_total_post, DATE_FORMAT(DATE(CreatedDate), '%d %b, %y') as date, COUNT
(ActivityID) as total
FROM (`Activity`)
WHERE DATE(CreatedDate) BETWEEN '2015-08-31' AND '2015-09-07'
GROUP BY `date`
ORDER BY `CreatedDate` ASC

Answer 1

你可以试试-

SELECT DATE_FORMAT(a.date_field, '%d %b, %y') AS 'DATE', 
COUNT(IF(b.CreatedDate>='2015-08-31 00:00:00' AND b.CreatedDate<='2015-09-07 23:59:59',b.ActivityID,NULL)) AS total_post, 
COUNT(IF(b.CreatedDate>='2015-08-23 00:00:00' AND b.CreatedDate<='2015-08-30 23:59:59',b.ActivityID,NULL)) AS previous_total_post, 
COUNT(b.ActivityID) total 
from 
(
  SELECT date_format(date_field, '%Y-%m-%d') as date_field FROM
(
    SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-2) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B
        ORDER BY daynum
    ) AA
) AAA
WHERE date_field >= '2015-08-23' and date_field < '2015-09-08') a 
LEFT JOIN `Activity` AS b ON a.date_field=DATE(b.CreatedDate) 
GROUP BY a.date_field
ORDER BY a.date_field ASC

请更新多年查询-

    SELECT DATE_FORMAT(a.date_field, '%d %b, %y') AS 'DATE', 
    COUNT(IF(b.CreatedDate>='2015-08-31 00:00:00' AND b.CreatedDate<='2015-09-07 23:59:59',b.ActivityID,NULL)) AS total_post, 
    COUNT(IF(b.CreatedDate>='2015-08-23 00:00:00' AND b.CreatedDate<='2015-08-30 23:59:59',b.ActivityID,NULL)) AS previous_total_post, 
    COUNT(b.ActivityID) total 
    from 
    (
      SELECT date_format(date_field, '%Y-%m-%d') as date_field FROM
    (
SELECT
    MAKEDATE(YEAR(SUBDATE(NOW(),INTERVAL 5 YEAR)),1) +
    INTERVAL (MONTH(NOW())-9) MONTH +
    INTERVAL daynum DAY date_field
FROM (
    SELECT m*1000+h*100+t*10+u daynum
    FROM
 (SELECT 0 m UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D,
    (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) C,
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B
    ORDER BY daynum
        ) AA
    ) AAA
    WHERE date_field >= '2010-01-01' and date_field < '2015-09-08') a 
    LEFT JOIN `Activity` AS b ON a.date_field=DATE(b.CreatedDate) 
    GROUP BY a.date_field
    ORDER BY a.date_field ASC