MYSQL 包括项目计数为零的日期值

我想统计购买总数以及一段时间内按 item_id 的购买次数。在此示例中，用户可以拥有商品，并且其他用户可以购买这些商品。所有者不能购买自己的物品。

我遇到的问题是如何在没有购买的日子以及正整数计数的日子中返回计数为“0”的结果。

这是我的表格：

      items           |          items_purchased          | numbers |   dates
i_id  item_id user_id | p_id item_id  user_id     date    |   num   | datefield
  1      1       11   |  1      1         13   2009-01-11 | 1       | 2005-06-07
  2      2       12   |  2      1         14   2009-01-11 | 2       | 2005-06-08
  3      3       11   |  3      2         15   2009-01-12 | 3       | 2005-06-09   
                      |  4      3         16   2009-01-12 | ...     | ...
                      |  5      1         17   2011-12-12 | 1000    | 2015-06-07

这是我的 MYSQL 查询购买总数user_id=11的物品：

SELECT COUNT(*) as counts, DATE(purchase_date) as DATE
FROM items_purchased 
JOIN items on items_purchased.item_id=items.item_id 
WHERE items.user_id=11 
GROUP BY DATE(purchase_date)
//note this query **doesn't** make use of the numbers and dates tables b/c I don't know how to use them

结果如下：

counts    date
  2    2009-01-11
  1    2009-01-12
  1    2011-12-12

这是我想看到的：

counts    date
  2    2009-01-11
  1    2009-01-12
  0    2009-01-13
  0    ... // should be a row here for each day between 2009-01-13 and 2011-12-12
  1    2011-12-12
  0    ... // should be a row here for each day between 2011-12-12 and current date
  0    current date (2012-6-27)

这是我的 MYSQL 查询，查询购买总数限制为item_id=1其所有者为user_id=11:

SELECT COUNT(*) as counts, DATE(purchase_date) as DATE
FROM items_purchased 
JOIN items on items_purchased.item_id=items.item_id 
WHERE items.user_id=11 and items.item_id=1
GROUP BY DATE(purchase_date)

结果如下：

counts    date
  2    2009-01-11
  1    2011-12-12

与上面类似，我想看到的是：

counts    date
  2    2009-01-11
  0    2009-01-12
  0    ... // should be a row here for each day between 2009-01-12 and 2011-12-12
  1    2011-12-12
  0    ... // should be a row here for each day between 2011-12-12 and current date
  0    current date (2012-6-27)

不知何故，我认为我需要将numbers and dates表，但我不知道如何做到这一点。任何想法将不胜感激，

谢谢， 蒂姆

编辑更正答案：

http://sqlfiddle.com/#!2/ae665/4 http://sqlfiddle.com/#!2/ae665/4

SELECT date_format(datefield,'%Y-%m-%d') AS DATE, IFNULL(counts, 0), item_id FROM 
    dates a
LEFT JOIN 
    (SELECT COUNT(*) as counts, purchase_date,user_id,item_id 
     FROM items_purchased 
     WHERE item_id=1
     GROUP BY date(purchase_date),item_id )r 
ON date(a.datefield) = date(r.purchase_date) ;

上述查询基于以下假设：

1. 表日期包含您要列出的日期范围内的连续日期。
2. 不太确定项目表的用途。第二个查询是按 items_purchased 表的 buy_date 和 item_id 进行分组。
3. 计数是对特定日期购买的特定商品进行计数（与 user_id 无关）。

@timpeterson 更新 (OP) 主要感谢@Sel。这是 sqlfiddles 演示了我感兴趣的两个查询：

1. 单个用户拥有的所有商品的每日购买量（例如，user_id=11): http://sqlfiddle.com/#!2/76c00/3 http://sqlfiddle.com/#!2/76c00/3
2. 每天的购买量为item_id=1其所有者为user_id=11: http://sqlfiddle.com/#!2/76c00/1 http://sqlfiddle.com/#!2/76c00/1

这是第二个的 SQL 代码，以防链接因某种原因损坏：

SELECT date_format(datefield,'%Y-%m-%d') AS DATE, 
IFNULL(countItem, 0), item_id
FROM dates a
LEFT JOIN 
(SELECT countItem, purchase_date,i.user_id,p.item_id FROM (
   SELECT count(*) as countItem, purchase_date,user_id,item_id 
   FROM items_purchased 
   GROUP BY date(purchase_date),item_id
   ) p 
 inner join items i
 on i.item_id=p.item_id
 WHERE p.item_id='1' and i.user_id='11' //just get rid of "p.item_id='1'" to produce the 1st query result
)r 
ON date(a.datefield) = date(r.purchase_date);