计算机知识数据库,【求助】我真傻.........求助计算机数据库知识..

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

9dfc1b5e-1571-4c3a-94d5-1a9fdbf183a7{建议使用GUID}

最有影响力吧友排名{方案名称}

{#BARNAME}吧贡献分前{#QUOTA}位

迪尼实验室

http://soft.de86.cn{开发者联系URL}

Denie

1.0{方案的展示版本号}

2009-05-15 06:30:00{方案的发布更新日期,格式:YYYY-MM-DD hh:mm:ss}

这个方案实现了经典的百度贴吧统计风暴吧友数据查询。

使用公式: (主题数*20 + 精品数*200 + 获得跟帖数/10)*((精品数 + 主题数)/(主题数*3))

{方案简短功能介绍}

{下面是SQL主体}

select

TopicAuthor as [用户ID],

TopicNumTotal as [主题数],

PrimeTopicNumTotal as [精品数],

round(TopicNumTotal * 1.00 / (select count(a.TopicId) from TBSQL_Tieba_{#TABLEID}_TopicList a Where a.TopicBarId = {#BARID}) *100 , 2) as [主题率(%)],

case when PrimeTopicNumTotal > 0 then round(PrimeTopicNumTotal * 1.00 / TopicNumTotal *100 , 2) else '0' end as [精品率(%)],

(TopicNumTotal * 20 + PrimeTopicNumTotal * 200 + ReplyNumTotal / 10) * round(((PrimeTopicNumTotal * 1.0 + TopicNumTotal * 1.0) / (TopicNumTotal * 3.0)) , 1) as [贡献分]

From

(

SELECT

count( a.TopicId ) AS TopicNumTotal ,

a.TopicAuthor As TopicAuthor,

sum(case when a.TopicIsPrime = 1 then 1 else 0 end) AS PrimeTopicNumTotal ,

sum( a.TopicClickNum ) AS ClickNumTotal ,

sum( a.TopicReplyNum ) AS ReplyNumTotal

FROM

TBSQL_Tieba_{#TABLEID}_TopicList a

GROUP BY

a.TopicAuthor

HAVING a.TopicBarId = {#BARID}

)

ORDER BY

[贡献分] DESC

LIMIT

{#QUOTA}