MYSQL包含零项目计数的日期值

Question

我想计算购买总数以及随着时间的推移按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和dates表，但是我不确定如何做到这一点。任何想法将不胜感激，

谢谢，蒂姆

Answer 1

为更正答案而编辑：

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. 不太确定项目表的用途。第二个查询是按item_purchased表的purchase_date和item_id分组。
3. 计数是对特定日期购买的特定商品进行计数（与user_id无关）。
   
   

@timpeterson（OP）进行的更新非常感谢@Sel。 这是sqlfiddles，展示了我感兴趣的两个查询：

1. 单个用户拥有的所有商品（例如user_id=11）的每日购买量：http：//sqlfiddle.com/#！2 / 76c00 / 3
2. 每天item_id=1拥有的购买次数user_id=11：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);