WordPress MySQL数据库：如何查询数周未发帖用户（禁用MAX(date)在SELECT子句）

问题背景 #

需要在WordPress的MySQL数据库中查询数周未发帖的用户，限制条件为不能在SELECT子句中使用MAX(date)函数。

当前尝试的SQL无法返回结果：

select wp_users.user_nicename, wp_users.user_email, max(yearweek(wp_wpforo_posts.created)) as w
from LeBearCNC.wp_users, wp_wpforo_posts
where wp_users.ID = wp_wpforo_posts.userid
and not exists (select max(yearweek(wp_wpforo_posts.created)) where wp_users.ID = wp_wpforo_posts.userid) /*and yearweek(current_date()) - 12 < yearweek(wp_wpforo_posts.created))*/
group by wp_users.user_nicename, wp_users.user_email
order by w desc;

若将MAX(yearweek(wp_wpforo_posts.created))放入WHERE子句，会触发invalid use of group function错误。

注：wp_wpforo_*表属于wpForo论坛插件。

已通过临时表实现需求，但希望优化为无需临时表的方案：

create temporary table if not exists lastpost as 
(select wp_wpforo_posts.userid,max(date(wp_wpforo_posts.created)) as w
from wp_wpforo_posts
group by userid);

SELECT wp_users.display_name, wp_users.user_email, lastpost.w
from wp_users, lastpost
where lastpost.userid = wp_users.ID
and date_add(lastpost.w, interval 6 month) < date(current_date())
order by w desc;

无临时表优化方案 #

方案1：使用关联子查询 #

通过子查询预先筛选每个用户的最后发帖记录，再与用户表关联筛选：

SELECT 
    wp_users.display_name, 
    wp_users.user_email,
    sub.w AS last_post_date
FROM wp_users
INNER JOIN (
    SELECT 
        userid,
        date(created) AS w
    FROM wp_wpforo_posts p
    WHERE NOT EXISTS (
        SELECT 1 
        FROM wp_wpforo_posts p2 
        WHERE p2.userid = p.userid 
        AND p2.created > p.created
    )
) AS sub ON wp_users.ID = sub.userid
WHERE DATE_ADD(sub.w, INTERVAL 12 WEEK) < CURRENT_DATE() -- 替换为你需要的"数周"，示例为12周
ORDER BY sub.w DESC;

逻辑说明：子查询通过NOT EXISTS排除掉用户所有非最新的帖子，直接获取每个用户的最后发帖日期；后续关联用户表，筛选出超过指定周数未发帖的用户。

方案2：使用窗口函数（MySQL 8.0+支持） #

利用ROW_NUMBER()窗口函数给每个用户的帖子按时间倒序编号，取编号为1的最新帖子：

SELECT 
    u.display_name, 
    u.user_email,
    p.last_post_date
FROM wp_users u
INNER JOIN (
    SELECT 
        userid,
        date(created) AS last_post_date,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY created DESC) AS rn
    FROM wp_wpforo_posts
) p ON u.ID = p.userid AND p.rn = 1
WHERE DATE_ADD(p.last_post_date, INTERVAL 12 WEEK) < CURRENT_DATE()
ORDER BY p.last_post_date DESC;

逻辑说明：窗口函数按用户分组，帖子创建时间倒序排序，每组第一条即为用户最后发帖记录；后续筛选逻辑同方案1。

内容的提问来源于stack exchange，提问作者Bernard Grosperrin