如何从没有统计数据的天，从Mysql获取数据 [英] How to get data back from Mysql for days that have no statistics

问题描述

我想从一个时间段（例如一个星期）获得注册的数量，这不是那么难，但我想知道是否可能在MySQL中返回一个零的天

I want to get the number of Registrations back from a time period (say a week), which isn't that hard to do, but I was wondering if it is in anyway possible to in MySQL to return a zero for days that have no registrations.

例如：
DATA：

An example: DATA:

ID_Profile    datCreate
1   2009-02-25 16:45:58
2   2009-02-25 16:45:58
3   2009-02-25 16:45:58
4   2009-02-26 10:23:39
5   2009-02-27 15:07:56
6   2009-03-05 11:57:30

SQL：

    SELECT 
        DAY(datCreate) as RegistrationDate, 
    COUNT(ID_Profile) as NumberOfRegistrations 
    FROM tbl_profile
    WHERE DATE(datCreate) > DATE_SUB(CURDATE(),INTERVAL 9 DAY)
    GROUP BY RegistrationDate
    ORDER BY datCreate ASC;

在这种情况下，结果是：

In this case the result would be:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
5   1

显然，我错过了几天之间。目前我正在解决这个在我的PHP代码，但我想知道MySQL是否有任何方法自动返回0缺少的天/行。这将是所需的结果：

Obviously I'm missing a couple of days in between. Currently I'm solving this in my php code, but I was wondering if MySQL has any way to automatically return 0 for the missing days/rows. This would be the desired result:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
28      0
1       0
2       0
3       0
4       0
5   1

这样我们可以使用MySQL来解决任何有关一个月中的天数问题，而不是依赖php代码来计算每个月有多少天，因为MySQL有这个功能内置。

This way we can use MySQL to solve any problems concerning the number of days in a month instead of relying on php code to calculate for each month how many days there are, since MySQL has this functionality build in.

提前感谢

推荐答案

以创建具有日期主键的单列表，并预加载每日的日期。你会有从你最早的起点到一些遥远的未来的日期。

No, but one workaround would be to create a single-column table with a date primary key, preloaded with dates for each day. You'd have dates from your earliest starting point right through to some far off future.

现在，您可以根据它来LEFT加入您的统计数据，然后您将获得没有数据的那些天的null。如果你真的想要一个零而不是null，请使用 IFNULL（colname，0）

Now, you can LEFT JOIN your statistical data against it - then you'll get nulls for those days with no data. If you really want a zero rather than null, use IFNULL(colname, 0)

这篇关于如何从没有统计数据的天，从Mysql获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！