php - WordPress $wpdb->get_results 即使为空也会返回日期

Question

我有一个看起来像这样的表格:

id | date       | count  
-----------------------
1  | 2016-07-13 | 2  
2  | 2016-07-13 | 3  
3  | 2016-07-13 | 1  
4  | 2016-07-15 | 12  
5  | 2016-07-15 | 1  
6  | 2016-07-15 | 4

这是我的查询

$test_query = $wpdb->get_results("
  SELECT 
    SUM(count) AS count, 
    date 
  FROM 
    ".$wpdb->prefix."statistics 
  WHERE 
    date BETWEEN DATE_SUB('2016-07-15', INTERVAL 2 DAY) AND '2016-07-15' 
  GROUP BY DATE(date)
");

我运行查询来获取结果

2016-07-13 | 6
2016-07-15 | 17

这个工作很好，只是我有 3 天的时间间隔，并且 2016-07-14 日期的结果没有显示。我使用的图表中的值需要为空或 0。

有没有办法让 2016-07-14 的值为 0？

2016-07-13 | 6
2016-07-14 | 0
2016-07-15 | 17

Answer 1

您可以尝试以下查询:

SELECT
v.selected_date,
COALESCE(YT.count,0) AS count
FROM
(select adddate(DATE_SUB('2016-07-15', INTERVAL 2 DAY),t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v

LEFT JOIN
(
    SELECT 
    SUM(count) AS count, 
    date 
  FROM 
    statistics #Your table name
  WHERE 
    date BETWEEN DATE_SUB('2016-07-15', INTERVAL 2 DAY) AND '2016-07-15' 
  GROUP BY DATE(date)
) AS YT 
ON YT.date = v.selected_date 
WHERE v.selected_date BETWEEN DATE_SUB('2016-07-15', INTERVAL 2 DAY) AND '2016-07-15' ;

注意:

突出显示的部分将根据您给定的日期范围而有所不同。

在第一个突出显示的部分中输入日期范围的开始日期。

在第二个和第三个突出显示的部分中，输入您的整个日期范围，就像您在给定查询中所做的那样。