php - 从 mysql "count()"返回计数 0

Question

我在 mysql 上运行了以下查询

SELECT DATE(field) AS day, COUNT( * ) AS totalSessions
FROM table
GROUP BY DATE(field) 
ORDER BY field DESC 
LIMIT 7

返回

day        |   totalSessions
2013-12-17 |   5
2013-12-15 |   1

我需要对查询进行哪些更改才能获得结果

day        |  totalSessions
2013-12-17 |  5
2013-12-16 |  0
2013-12-15 |  1
2013-12-14 |  0
2013-12-13 |  0
2013-12-12 |  0
2013-12-11 |  0

Answer 1

您可能需要(在某处)存储要返回的日期。我认为存储过程可以帮助您:

delimiter $$
create procedure getStuff(d0 date, d1 date)
begin
    declare d date;
    drop table if exists temp_dates;
    create temporary table temp_dates (
         d date not null primary key
    );
    set d = d0;
    while d <= d1 do
        insert into temp_dates values (d);
        set d = date_add(d, interval +1 day);
    end while;
    select 
        a.d as day,
        count(b.field) as totalSessions
    from
        temp_dates as a
        left join yourTable as b on a.d = b.dateField -- Assuming "dateField" holds the date
    group by
        a.d;
end $$
delimiter ;

希望对你有帮助