如何返回零导致列，如果在ADDED_DATE没有发现MySQL查询

问题描述：

MySQL查询没有值，以获得结果ADDED_DATE记录：如何返回零导致列，如果在ADDED_DATE没有发现MySQL查询

SELECT added_date, count(added_date) AS result 
FROM reports 
WHERE added_date BETWEEN '2017-08-01' AND '2017-08-10' 
GROUP BY added_date

查询结果：

added_date result 
2017-08-01 20 
2017-08-02 10 
2017-08-04 22 
2017-08-08 1

我想结果为：

added_date result 
    2017-08-01 20 
    2017-08-02 10 
    2017-08-03 0 
    2017-08-04 22 
    2017-08-05 0 
    2017-08-06 0 
    2017-08-07 0 
    2017-08-08 1

的可能的复制[mysql如何填写范围缺少的日期？（https://stackoverflow.com/questions/ 3538858/MySQL的 - 如何对填充失踪日期，在范围内） – Riedsio

答

如下面的代码所示。您可以创建日历表。其中提供我们所需的确切日历。进入该日历表。

DROP TABLE IF EXISTS time_dimension; 

CREATE TABLE time_dimension (
     id      INTEGER PRIMARY KEY, -- year*10000+month*100+day 
     db_date     DATE NOT NULL, 
     year     INTEGER NOT NULL, 
     month     INTEGER NOT NULL, -- 1 to 12 
     day      INTEGER NOT NULL, -- 1 to 31 
     quarter     INTEGER NOT NULL, -- 1 to 4 
     week     INTEGER NOT NULL, -- 1 to 52/53 
     day_name    VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'... 
     month_name    VARCHAR(9) NOT NULL, -- 'January', 'February'... 
     holiday_flag   CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')), 
     weekend_flag   CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')), 
     event     VARCHAR(50), 
     UNIQUE td_ymd_idx (year,month,day), 
     UNIQUE td_dbdate_idx (db_date) 

) Engine=MyISAM; 

DROP PROCEDURE IF EXISTS fill_date_dimension; 
DELIMITER // 
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE) 
BEGIN 
    DECLARE currentdate DATE; 
    SET currentdate = startdate; 
    WHILE currentdate < stopdate DO 
     INSERT INTO time_dimension VALUES (
         YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate), 
         currentdate, 
         YEAR(currentdate), 
         MONTH(currentdate), 
         DAY(currentdate), 
         QUARTER(currentdate), 
         WEEKOFYEAR(currentdate), 
         DATE_FORMAT(currentdate,'%W'), 
         DATE_FORMAT(currentdate,'%M'), 
         'f', 
         CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END, 
         NULL); 
     SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY); 
    END WHILE; 
END 
// 
DELIMITER ; 

TRUNCATE TABLE time_dimension; 

CALL fill_date_dimension('2000-01-01','2018-01-01');

一旦创建了该日历表，您可以在下面写下查询，它将解决您的问题。

SELECT added_date,count(added_date) as result 
FROM time_dimension TD 
LEFT JOIN reports RP ON DATE(RP.added_date)=TD.DB_DATE 
WHERE added_date BETWEEN '2017-08-01' AND '2017-08-10' group by 
GROUP BY DATE(DB_DATE);

答

此链接提供了干净的结果对上述问题

http://www.media-division.com/using-mysql-generate-daily-sales-reports-filled-gaps/

如何返回零导致列，如果在ADDED_DATE没有发现MySQL查询

相关推荐