MySQL Group By Date的NULL值 [英] MySQL Group By Date for NULL values

问题描述

我试图返回每天运行一个商务站点的订单数，但是对于有0个订单的日子，我的sql语句返回null，因此它们是数据空白.这些天我该如何插入0，以确保数据完整?

I am trying to return the number of orders for every day a commerce site has been live, but for days where there were 0 orders my sql statement returns null, and hence their is a gap in the data. How can I insert a 0 into these days so the data is complete?

    $sql = "SELECT COUNT(*) AS orders
        FROM `orders`
        GROUP BY DATE(`order_datetime`)
        ORDER BY DATE(`order_time`) ASC";

    $query = $this->db->query($sql);

我曾尝试像这样使用ifnull，但得到的结果与上述相同:

I have tried using ifnull like so but I get the same result as above:

    $sql = "SELECT ifnull(COUNT(*),0) AS orders
        FROM `orders`
        GROUP BY DATE(`order_datetime`)
        ORDER BY DATE(`order_time`) ASC";

    $query = $this->db->query($sql);

推荐答案

您可以使用COALESCE吗?

can you use COALESCE?

例如:

SELECT COALESCE(COUNT(*),0) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC

它返回列表中的第一个非null值-如果它不为null，则计数，如果不是，则为0.

it returns first non null value in the list - count if its not null and 0 if it is.

http://dev.mysql.com /doc/refman/5.0/en/comparison-operators.html#function_coalesce

等等..问题是没有那些日期的行，所以您只获得那些日期的结果?

hang on.. is the issue that there are no rows with those dates so you just get results for those dates?

如果这样的话，您也可以返回订购日期，并且对显示结果的方式更加了解:

if so you could return the order date too and be a bit more clever about how you display your result:

SELECT  order_datetime,COUNT(*) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC

然后，在输出结果时，可能会创建一个循环，循环遍历您感兴趣的日期，如果该日期没有行，则打印为0:

then when outputting your results maybe create a loop that iterates through the dates you are interested in and prints a 0 if there is no row for that date:

for (datetime  d = startdate; d <= enddate; datetime.adddays(1)){
    if (recordset[order_datetime] == d){
        output (d,recordset[orders]);
        recordset.movenext();
    }else
    {
        output (d,0);
    }
}

(请注意，我不知道那是什么编程语言；-)

(note i have no idea what programming language that would be ;-)

如果您想在数据库中完成所有操作，请查看此SO问题，以了解如何动态创建日期表，然后可以将外部联接留给查询在日期上.

if you want to do it all in the DB then look at this SO question for how to create a table of the dates on the fly and then you can left outer join from it to you query on date..

获取两个日期之间的日期列表

这篇关于MySQL Group By Date的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！