100分求一个SQL语句！！

[natrium11]

faint!!好久没来CSDN了，本来可给300的，现在怎么只能给100了？

我做了一个基于ASP，ACCESS的BBS系统，需要一个查询来做用户得分排名。

和这个查询相关的有两个表users,articles

users有字段 id,view_count,login_count

articles有字段 id, author_id, reply_to, score, oscore, oscore_count

users.id,articles.author_id构成外键

articles.reply_to若为null表示是话题，否则是回复，其值为所回复的话题的ID

只有对话题帖子记分，分数有两种给法，一种是在回复中打，记在该回复帖子记录的score字段中，另一种直接打，总分记录在话题帖子的oscore字

段中，分数数目记录在oscore_count中。

用户得分=所有所发话题帖子得分之和+所发回复帖子数*6+20*users.login_count（对应该用户）+users.view_count（对应该用户）

所发帖子得分=（所有回复帖子给分之和+articles.oscore（对应该帖子））/（回复帖子数+articles.oscore_count（对应该帖子））

（回复帖子的标记是其reply_to字段值为我的话题帖子的id）

要求将用户得分按不同用户分组，降序排列。

多谢多谢！！！！！

[Yang_]

select a.id,sum(d.score+d.cn*6)+20*a.login_count+a.view_count as score
from
users a left join
(
select b.author_id,sum((c.score+b.oscore)/(c.cn+b.oscore_count)) as score,sum(c.cn) as cn
from articles b left join
(
select reply_to, sum(score) as score,count(*) as cn from articles where reply_to is not null group by reply_to
) as c
on b.author_id=c.reply_to and b.reply_to is null
) as d

[Yang_]

select a.id,sum(d.score+d.cn*6)+20*a.login_count+a.view_count as score
from
users a left join
(
select b.author_id,sum((c.score+b.oscore)/(c.cn+b.oscore_count)) as score,sum(c.cn) as cn
from articles b left join
(
select reply_to, sum(score) as score,count(*) as cn from articles where reply_to is not null group by reply_to
) as c
on b.author_id=c.reply_to and b.reply_to is null
group by b.author_id
) as d
group by a.id