mysql - 需要有关 MySQL 的帮助，加入两个查询

Question

我正在运行 WordPress，我想编写一个自定义查询，以显示基于一个特定类别的每月帖子存档。我使用 Debug模式找出如何根据所有帖子获取每月存档，选择特定类别。以下是查询:

获取每月存档:

SELECT YEAR(post_date) AS `year`, MONTH (post_date) AS `month`, 
  count(ID) as posts FROM wp_posts  
  WHERE post_type = 'post' AND post_status = 'publish' 
  GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

选择类别:

SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy
  AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy 
  IN ('category')  AND t.slug = 'blog' ORDER BY t.name ASC

我不知道如何加入这两个 ;) 感谢任何帮助!

谢谢。

更新:

还涉及另一个名为 term_relationships 的表，因此总共有 4 个表。这是 WordPress 数据库结构的图像:http://codex.wordpress.org/images/8/83/WP_27_dbsERD.png

无论如何，这就是我所在的位置:

SELECT t.*, tt.*, YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, 
    count(ID) as posts FROM wp_posts, wp_term_relationships AS tr, 
    wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id 
    WHERE tt.taxonomy IN ('category') AND t.slug = 'blog' 
    AND post_type = 'post' AND post_status = 'publish' 
    AND ID = tr.object_id AND tr.term_taxonomy_id = t.term_id 
    GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

我知道这不是最好的解决方法，这就是为什么我需要你的帮助 ;)

谢谢。

Answer 1

像这样的东西如何作为连接的基础以及您需要的 where 子句添加:

    SELECT 
        YEAR(post_date) AS `year`, 
        MONTH (post_date) AS `month`, 
        count(ID) as posts  
    FROM wp_posts 
    INNER JOIN wp_term_relationships wtr ON wp_posts.id = wtr.object_id
    INNER JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
    INNER JOIN wp_terms wt ON wtt.term_id = wt.term_id
    WHERE post_type = 'post' AND post_status = 'publish' 
    AND wtt.taxonomy = 'category'
    AND wt.name = 'enter category name here'
    GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC