通过值连接多维数组

Question

您好，我有一个小问题，我查询mysql，检索json编码的数据列表，并绘制成图形(例如，数量与日期轴)。但是，sql不会在没有数据的情况下返回查询数据的日期。因此，在图表上有缺失的日期。

我在这里读到过，有些人只是为了填补空白而回避创建其他表的工作。这对我来说不是一个选择。

我有一个解决方案，可以在查询中创建一个包含所有日期的数组。

有人能建议一种方法将其压缩到我的查询结果的多维数组中吗？希望是通过某种聪明的方法。

或者，是否有我不确定的SQL函数可以填充空白日期？

我的查询目前看起来像这样：

    SELECT SQL_CALC_FOUND_ROWS Date, SUM(call_answer) AS call_answer, SUM(call_busy) AS call_busy, SUM(call_noanswer) AS call_noanswer, SUM(call_notrec) as call_notrec
    FROM ( 
        SELECT * FROM ( 
            SELECT substring(dateCreated, 1,10) AS Date, COUNT(id) as call_answer, 0 as call_busy, 0 as call_noanswer, 0 as call_notrec
            FROM cdr 
            WHERE  (company = '8346767432431') AND (dateCreated >= '2012-10-16' ) AND (dateCreated <= '2012-11-16' )  AND dialStatus = 'NORMAL_CLEARING' GROUP BY Date ) 
            AS t_0 
            UNION
            SELECT * FROM ( 
            SELECT substring(dateCreated, 1,10) AS Date, 0 as call_answer, COUNT(id) as call_busy, 0 as call_noanswer, 0 as call_notrec
            FROM cdr 
            WHERE  (company = '8346767432431') AND (dateCreated >= '2012-10-16' ) AND (dateCreated <= '2012-11-16' )  AND dialStatus = 'USER_BUSY' GROUP BY Date )
            AS t_1
            UNION
            SELECT * FROM ( 
            SELECT substring(dateCreated, 1,10) AS Date, 0 as call_answer, 0 as call_busy, COUNT(id) as call_noanswer, 0 as call_notrec
            FROM cdr 
            WHERE  (company = '8346767432431') AND (dateCreated >= '2012-10-16' ) AND (dateCreated <= '2012-11-16' )  AND (dialStatus = 'ORIGINATOR_CANCEL' OR dialStatus = 'NO_USER_RESPONSE') GROUP BY Date )
            AS t_2
            UNION
            SELECT * FROM ( 
            SELECT substring(dateCreated, 1,10) AS Date, 0 as call_answer, 0 as call_busy, 0 as call_noanswer, COUNT(id) as call_notrec
            FROM cdr 
            WHERE  (company = '8346767432431') AND (dateCreated >= '2012-10-16' ) AND (dateCreated <= '2012-11-16' )  AND (dialStatus = 'UNALLOCATED_NUMBER' OR dialStatus = 'INVALID_NUMBER_FORMAT') GROUP BY Date )
            AS t_3

    )

    AS t_total
    GROUP BY Date;

所以已经相当混乱了。

Answer 1

您可以利用MySQL中的比较结果是一个数字这一事实来简化SQL查询；0表示false，1表示true。所以你可以对比较求和来计算你的计数。

SELECT DATE(dateCreated) AS `Date`,
 SUM(dialStatus = 'NORMAL_CLEARING') AS call_answer,
 SUM(dialStatus = 'USER_BUSY') AS call_busy,
 SUM(dialStatus = 'ORIGINATOR_CANCEL') AS call_noanswer,
 SUM(dialStatus = 'UNALLOCATED_NUMBER') AS call_notrec
FROM cdr 
WHERE company = '8346767432431'
  AND dateCreated BETWEEN '2012-10-16' AND '2012-11-16' -- = 11-16 0:00 !
GROUP BY DATE(dateCreated) ASC

请注意，无论是在此查询中还是在原始查询中，范围的结束都将隐含地假定时间为0:00。所以你不会把那一天包括在内，除了第一秒。您可能需要对此进行调整。

至于如何组合的实际问题:上面的查询以升序生成日期。因此，使用您的日期列表，看看该列表中的第一项是否等于下一条记录的日期。但事实并非如此，请在结果数据中插入一行零，并从列表中的下一个日期继续，同时保持查询中的当前记录不变。