php - 查找最近 n 个帖子中的用户帖子数

Question

我正在尝试找到最有效的方法来查找表的最后 n 行中用户的帖子数。我试过了

$db->query_first("SELECT COUNT(*) AS
user_recent_posts FROM " . TABLE_PREFIX . "post WHERE userid = $userid
AND threadid = '" . $threadinfo['threadid'] . "' AND visible = '1'
ORDER BY dateline DESC LIMIT 10" );

我正在尝试获取线程中最后 10 个帖子中的用户帖子 .. 但这显然会返回用户的最后 10 个帖子，无论它们是否在最后 10 个帖子中

Answer 1

使用子选择首先获取 n 行，然后计算用户的行数:

$db->query_first("
  SELECT COUNT(b.*) AS user_recent_posts 
  FROM (
    SELECT a.* 
    FROM " . TABLE_PREFIX . "post a
    AND a.threadid = '" . $threadinfo['threadid'] . "' AND a.visible = '1'
    ORDER BY a.dateline DESC LIMIT 10) b
  WHERE b.userid = $userid" );