我试图获得特定项目在给定月份中的查看次数。
我有一个看起来像这样的表：
id_component viewed_date 1 | 2013-03-25 11:40:04 3 | 2013-03-26 11:41:04 4 | 2013-04-05 11:42:04 2 | 2013-04-07 11:43:04 3 | 2013-04-12 11:44:04 4 | 2013-04-17 11:45:04 3 | 2013-04-18 11:46:04 2 | 2013-04-20 11:47:04 4 | 2013-04-22 11:48:04我有一个这样的组件表：
id, component_name, component_desc, component_price, folder_id 1 | muffler | ford focus muffler | 99.00 | 3 2 | air filter| ford focus air filter | 12.00 | 3 3 | oil filter| ford focus oil filter | 6.00 | 3 4 | wiper | ford focus rear wiper | 6.00 | 3我目前的查询是这样的：
SELECT MONTHNAME(viewed_date) AS vmonth, YEAR(viewed_date) AS vyear, COUNT(*) AS vcount FROM viewed AS V RIGHT JOIN components AS C on V.id_component = C.id WHERE id_component IN (SELECT id FROM components WHERE folder_id = '3') GROUP BY YEAR(V.viewed_date), MONTH(V.viewed_date) ORDER BY V.viewed_date我想要回来的是：
vmonth = march, vyear = 2012, vcount = 1, id = 1, component_name = muffler, etc. etc. vmonth = april, vyear = 2012, vcount = 1, id = 2, component_name = air filter, etc. etc. vmonth = march, vyear = 2012, vcount = 1, id = 3, component_name = oil filter, etc. etc. vmonth = april, vyear = 2012, vcount = 2, id = 3, component_name = oil filter, etc. etc. vmonth = april, vyear = 2012, vcount = 3, id = 4, component_name = wiper, etc. etc.我得到了这个
vmonth = march, vyear = 2012, vcount = 4 vmonth = april, vyear = 2012, vcount = 9我没有获得联合数据或单个组件的数据......只是文件夹的每月总计
这是一件愚蠢的事我在做什么......有什么建议吗？
I'm trying to get the number of times a particular item has been viewed in a given month.
I have a viewed table that looks something like this:
id_component viewed_date 1 | 2013-03-25 11:40:04 3 | 2013-03-26 11:41:04 4 | 2013-04-05 11:42:04 2 | 2013-04-07 11:43:04 3 | 2013-04-12 11:44:04 4 | 2013-04-17 11:45:04 3 | 2013-04-18 11:46:04 2 | 2013-04-20 11:47:04 4 | 2013-04-22 11:48:04I have a component table something like this:
id, component_name, component_desc, component_price, folder_id 1 | muffler | ford focus muffler | 99.00 | 3 2 | air filter| ford focus air filter | 12.00 | 3 3 | oil filter| ford focus oil filter | 6.00 | 3 4 | wiper | ford focus rear wiper | 6.00 | 3My current query is this:
SELECT MONTHNAME(viewed_date) AS vmonth, YEAR(viewed_date) AS vyear, COUNT(*) AS vcount FROM viewed AS V RIGHT JOIN components AS C on V.id_component = C.id WHERE id_component IN (SELECT id FROM components WHERE folder_id = '3') GROUP BY YEAR(V.viewed_date), MONTH(V.viewed_date) ORDER BY V.viewed_dateWhat I want to get back is this:
vmonth = march, vyear = 2012, vcount = 1, id = 1, component_name = muffler, etc. etc. vmonth = april, vyear = 2012, vcount = 1, id = 2, component_name = air filter, etc. etc. vmonth = march, vyear = 2012, vcount = 1, id = 3, component_name = oil filter, etc. etc. vmonth = april, vyear = 2012, vcount = 2, id = 3, component_name = oil filter, etc. etc. vmonth = april, vyear = 2012, vcount = 3, id = 4, component_name = wiper, etc. etc.I'm getting this
vmonth = march, vyear = 2012, vcount = 4 vmonth = april, vyear = 2012, vcount = 9I'm not getting the joined data or the data on the individual components... just the monthly totals for the folder
It's gotta be something stupid I'm doing... any suggestions?
最满意答案
您只获得两行，因为您按一对（年，月）分组，在您查看的表中只有两个不同的值。 您需要将第三列 - id_component添加到组中以获得所需的结果。 此外，您似乎不希望每月，年和ID的每个组合都看到0，因此您需要INNER JOIN而不是外部。
此查询应该为您提供所需的输出：
SELECT v.vmonth, v.vyear, v.vcount, c.id, concat( c.component_name, ', etc. etc.' ) AS component_name FROM components c JOIN ( SELECT MONTHNAME( viewed_date ) AS vmonth, YEAR( viewed_date ) AS vyear, id_component, count( * ) AS vcount FROM viewed GROUP BY vmonth, vyear, id_component ) v ON v.id_component = c.idYou are getting only two rows because you group by a pair (year,month) of which you have only two distinct values in your viewed table. You need to add third column - id_component to the group by to get your desired result. Also it seems you don't want to see 0's for every combination of month, year and id, so you need INNER JOIN instead of outer one.
This query should give you desired output:
SELECT v.vmonth, v.vyear, v.vcount, c.id, concat( c.component_name, ', etc. etc.' ) AS component_name FROM components c JOIN ( SELECT MONTHNAME( viewed_date ) AS vmonth, YEAR( viewed_date ) AS vyear, id_component, count( * ) AS vcount FROM viewed GROUP BY vmonth, vyear, id_component ) v ON v.id_component = c.id更多推荐
发布评论