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查询,用于限制仅限于user\_id = 11拥有的item\_id = 1的购买总数：

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)

我以某种方式认为我需要合并数字和日期表,但是我不确定如何做到这一点.任何想法将不胜感激,

谢谢,
蒂姆

解决方法:

为更正答案而编辑：

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. Table dates contains sequential of dates that is within the range of date you want to list.
2. Not really sure what is the items table for. The second query is to group by purchase\_date and item\_id of items\_purchased table.
3. Count is to count particular item purchased on the particular day (regardless of user\_id).

@timpeterson(OP)的更新
非常感谢@Sel.这是sqlfiddles,展示了我感兴趣的两个查询：

单个用户拥有的所有商品的每日购买量(例如,user\_id = 11)：http://sqlfiddle.com/#!2/76c00/3
user\_id = 11拥有的item\_id = 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);