MYSQL包括零项计数的日期的值 [英] MYSQL including values for dates with zero item counts
问题描述
我想计算购买总次数以及item_id随时间的购买次数。在该示例中，用户可以拥有项目，并且这些项目可以由其他用户购买。所有者不能购买自己的项目。
我遇到的问题是如何返回带有计数0的结果，在没有正整数天的日子里没有购买的日子。
这是我的表：
items_purchased |数字|日期
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（*）作为计数，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这个查询**不**使用数字和日期表b / c我不知道如何使用它们
以下是结果：
计算日期
2 2009-01-11
1 2009-01-12
1 2011-12-12
'd喜欢看到：
计算日期
2 2009-01-11
1 2009- 01-12
0 2009-01-13
0 ... //在2009-01-13和2011-12-12之间的每一天都应该是一行
1 2011- 12-12
0 ... //在2011-12-12和当前日期之间的每一天都应该是一行
0当前日期（2012-6-27）
这是我的MYSQL查询限制为 item_id = 1 user_id = 11 ：
）作为计数，DATE（purchase_date）为DATE
FROM items_purchased
JOIN items on items_purchased.item_id = items.item_id
的项目WHERE items.user_id = 11和items.item_id = 1
GROUP BY DATE（purchase_date）
以下是结果：
计算日期
2 2009-01-11
1 2011-12-12
与上面类似，这里是我想看到的：
计数日期
2 2009-01-11
0 2009-01-12
0 ... //应该是2009-01-12之间每天的行， 2011-12-12
1 2011-12-12
0 ... //应该在2011-12-12和当前日期之间的每一天这一行
0当前日期2012-6-27）
我想我需要整合 和日期表，但我不知道如何做。任何想法都将非常感激，
感谢，
tim
已修改为正在修正的答案：
http://sqlfiddle.com/#!2/ae665/4
SELECT date_format（datefield， ％Y-％m-％d'）AS DATE，IFNULL（counts，0），item_id FROM
date a
LEFT JOIN
（SELECT COUNT（*）as count，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）;
上述查询基于以下假设：
< blockquote>
- 表日期包含在您要列出的日期范围内的连续日期。
- items表为。第二个查询是按照purchase_date和items_purchased表的item_id分组。
- 计数是计算在特定日期（不管user_id）购买的特定商品。
由@timpeterson（OP）更新
感谢@Sel。我感兴趣的两个查询：
- 单个用户拥有的所有项目的购买量/日（例如
user_id = 11）： http://sqlfiddle.com/#!2 / 76c00 / 3 - 由
user_id拥有的：item_id = 1= 11
http://sqlfiddle.com/# ！2 / 76c00 / 1
这里是第二个的SQL代码，以防链接被破坏： / p>
SELECT date_format（datefield，'％Y-％m-％d'）AS DATE，
IFNULL 0），item_id
FROM date 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日期（purchase_date），item_id
）p
内部连接项i
on i.item_id = p.item_id
WHERE p.item_id ='1'和i.user_id = '11'//只是去掉p.item_id ='1'产生第一个查询结果
）r
ON date （a.datefield）= date（r.purchase_date）;
I'd like to count the total number of purchases as well as the purchases by item_id over time. In this example, a user can own an item and these items can be purchased by other users. An owner can't purchase their own item.
The problem I'm having is how to return results with counts of "0" for days where there were no purchases alongside the days with positive integer counts.
Here's my tables:
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
Here's my MYSQL query for the total numbers of purchases of user_id=11's items:
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
Here's the results:
counts date
2 2009-01-11
1 2009-01-12
1 2011-12-12
Here's what I'd like to see instead:
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)
Here's my MYSQL query for the total numbers of purchases restricted to item_id=1 which is owned by 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)
Here's the results:
counts date
2 2009-01-11
1 2011-12-12
Similar to the above, here's what I'd like to see instead:
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)
Somehow I think I need to incorporate the numbers and dates tables but I'm not sure how to do this. Any thoughts would be greatly appreciated,
thanks, tim
EDITED FOR CORRECTING ANSWER:
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) ;
The above query is based on assumption:
- Table dates contains sequential of dates that is within the range of date you want to list.
- 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.
- Count is to count particular item purchased on the particular day (regardless of user_id).
UPDATE by @timpeterson (OP) Major thanks to @Sel. Here's sqlfiddles demonstrating both queries I'm interested in:
- Purchases/day for all items owned by a single user (e.g.,
user_id=11): http://sqlfiddle.com/#!2/76c00/3 - Purchases/day for
item_id=1which is owned byuser_id=11: http://sqlfiddle.com/#!2/76c00/1
here's the SQL code for the 2nd one in case the link gets broken somehow:
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包括零项计数的日期的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
