检索 INSERT 的第 N 个子查询

Question

摘要

从一个包含用户各种帖子的表到一个论坛，每天都会更新另一个表，其中包含前 20 个发帖者。帖子存储在 posts 中，每日高分存储在 hiscore 中。

表

posts:
   post_id(PK:INT) | user_id(INT) | ... | timestamp(TIMESTAMP)

hiscore:
   user_id(INT) | rank(INT)

查询

TRUNCATE TABLE `hiscore` ;
INSERT INTO `hiscore` (`user_id`,`rank`)
 (
   SELECT `user_id`, ???
   FROM `posts`
   WHERE `timestamp` BETWEEN blah AND blah
   GROUP BY `user_id`
   ORDER BY COUNT(`post_id`) DESC
   LIMIT 20
 )

实际问题

在上面的查询中应该插入什么而不是 ??? 来计算排名？

是否有像 @NTH_SUBQUERY 这样的变量可以在第五次运行 SELECT 子查询时替换 5？

---

更新：表hiscore应该仅包含前20名海报。我知道表结构可以优化。答案的重点应该是如何确定子查询当前检索的行。

Answer 1

INSERT INTO `hiscore` (`user_id`,`rank`)
 (
   SELECT `user_id`, @rank = @rank + 1
   FROM `posts`, (SELECT @rank := 0) r
   WHERE `timestamp` BETWEEN blah AND blah
   GROUP BY `user_id`
   ORDER BY COUNT(`post_id`) DESC
   LIMIT 20
 )

Answer 2

您似乎太喜欢截断，对于您的情况

hiscore:
   the_date (DATE) | user_id(INT) | rank(INT)

上构建了一个键

，并在the_date，rank 插入

set @pos=0;

insert into hiscore
select cur_date(), user_id, @pos:=@pos+1
from ...

以保持表大小易于管理，您可能可以几个月删除一次

或者您可以在 rank 上设置 auto_increment

create table hiscore
(
  the_date date not null,
  rank int(3) not null auto_increment,
  user_id int(10) not null,
  primary key (the_date, rank)
);

因此，排名会自动递增（这与按每日帖子数量降序排列相同）