从mysql“count（）”返回计数0 [英] Return counts of 0 from mysql "count()"

问题描述

我有以下查询运行mysql

I have the below query running on 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

我需要在查询时更改什么，才能获得结果

What would I need to change on my query so that I would get the results

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

推荐答案

您可能需要存储你想返回。我认为一个存储过程可以帮助你：

You may need to store (somewhere) the dates you want to return. I think a stored procedure can help you:

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 ;

希望这有助于

这篇关于从mysql“count（）”返回计数0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！