我有以下查询,每天对哪些条目进行分组和计数:
SELECT DISTINCT `year`, `month`, `day`, count(entry_id) as 'posts'
FROM (`exp_channel_titles`)
WHERE `status` = 'open'
GROUP BY `year`, `month`, `day`
ORDER BY `entry_date` desc
它工作正常,但是它从所有类别中选择,但是我想限制为一个或多个类别。
对于不熟悉EE数据库的人,这里有一些我们可能需要的表格示例
exp_channel_titles:
entry_id year month day
1 2011 11 5
2 2011 11 6
3 2011 11 7
exp_categories:
cat_id cat_name
1 sport
2 computer
3 car
exp_category_posts:将条目连接到类别
entry_id cat_id
1 2
1 2
2 1
3 3
最佳答案
SELECT ect.year, ect.month, ect.day, count(ect.entry_id) as posts
FROM exp_channel_titles as ect
INNER JOIN exp_category_posts ecp
ON ecp.entry_id=ect.entry_id
INNER JOIN exp_categories ec
ON ecp.cat_id=ec.cat_id
WHERE ect.status = 'open'
AND ec.cat_name = "computer" /* this is to filter by category name */
GROUP BY ect.year, ect.month, ect.day
ORDER BY ect.entry_date desc; /* where is this entry_date come from? */
关于mysql - ExpressEngine:自定义查询-每天对条目进行分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8465912/