我想计算一段时间内的购买总数以及按 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

这是我对 user_id=11 的商品的购买总数的 MYSQL 查询:

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)

不知何故，我认为我需要合并 numbers 和 dates 表，但我不确定如何执行此操作。任何想法将不胜感激，

谢谢， 蒂姆

最佳答案

为更正答案而编辑:

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:

1. 单个用户拥有的所有元素的购买量/天(例如，user_id=11):http://sqlfiddle.com/#!2/76c00/3
2. 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);

关于MYSQL 包括项目计数为零的日期值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11237268/