mysql - 使用 SQL 显示 WordPress 中的存档帖子

Question

我正在尝试像这样显示 WordPress 数据库中的“存档帖子”(但使用 ColdFusion 来显示数据)，并且需要有关 SQL 查询的帮助。

示例:

2019 年 1 月(1 篇文章)
2018 年 12 月(3 篇文章)
2017 年 9 月(1 篇文章)

我的查询如下所示:

SELECT      wp_posts.post_date, post_type, post_status
FROM        wp_posts
WHERE       wp_posts.post_type = "post"
AND         wp_posts.post_status = "publish"
GROUP BY        YEAR(post_date), MONTH(post_date) DESC

这给了我以下错误:

"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dnr_wordpress.wp_posts.post_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Answer 1

感谢 Ivar，这个查询对我有用:

    SELECT      YEAR(post_date) as year, MONTH(post_date) as month, COUNT(ID) as nr_of_posts
    FROM        wp_posts

    WHERE       wp_posts.post_type = "post"

    AND         wp_posts.post_status = "publish"


    GROUP BY YEAR(post_date ) , MONTH( post_date );