帖子表
topic_tid(主题Id) topic_title(主题标题) userId(用户ID) topicTime(发帖时间)
用户表
UserId username查询排行榜月度和双周的发帖最多的前10名用户 包括主题Id 用户名称 发帖数
topic_tid(主题Id) topic_title(主题标题) userId(用户ID) topicTime(发帖时间)
用户表
UserId username查询排行榜月度和双周的发帖最多的前10名用户 包括主题Id 用户名称 发帖数
from table_b b,
(
select a.*,den_rank() over(partition by [date] order by num desc) rn
from
(
select userid,to_char(topictime,'yyyy-mm') as [date],count(*) as num
from table_a
group by to_char(topictime,'yyyy-mm'),userid
)a
)c
where b.userid=c.userid and rn<=10
select topic_id,username,num,row_number() over(order by topictime) rn,topictime from(
select max(topic_id) topic_id,username,count(topic_id) num,max(topictime) topictime
from a,b where a.userid = b.userid(+) group by a.userid,username
)
) where topictime between sysdate-2*7 and sysdate and rn >= 10
--月数
select * from
(select t.*,rownum rn
from(select userid,to_char(topicTime,'yyyy-mm') topicTime,count(1) 发帖数,
max(topic_tid) topic_tid
from 帖子表 a,用户表 b
where a.userid=b.userid
group by userid,to_char(topicTime,'yyyy-mm')
order by count(1) desc) ) t where t.rn<=10
select * from
(select t.*,rownum rn
from(select userid,to_char(topicTime,'yyyy-mm') topicTime,count(1) 发帖数,
max(topic_tid) topic_tid
from 帖子表 a,用户表 b
where a.userid=b.userid
group by userid,to_char(topicTime,'yyyy-mm')
order by count(1) desc) ) t where t.rn<=10