文章统计：怎么样最近一周和所有文章数！
一个文章系统，原来是统计每个管理员发文章的总数
我用的是下面的ＳＱＬ

SELECT top 7 count(articleid) as Counts,Editor
FROM Admin INNER JOIN Article ON Admin.UserName = Article.Editor
where Article.Passed=True
group by Editor
order by count(articleid) desc

显示效果：

用 户 排 行

名次　用户名 文章总数
1 　　bobo 2０
2 　　xixi 1０

现在要把名次的地方换成该用户最近一周的发文章数，效果如下：

　用户名 最近一周 文章总数
bobo 4 2０
xixi 6 1０

用下面的SQL可以实现查询最近一周每个用户的发文章数

SELECT top 7 count(articleid) as Counts,Editor
FROM Admin INNER JOIN Article ON Admin.UserName = Article.Editor
where Article.Passed=True and DateDiff( 'd ',UpdateTime,#2007-3-21#) <7
group by Editor
order by count(articleid) desc

可是怎么用一条语句实现？





[解决办法]

SELECT
top 7 count(articleid) as Counts,
Editor ,
(select count(*) from Article where Editor=b.Editor and Passed=True)
FROM Admin as a
INNER JOIN Article as b ON a.UserName = b.Editor
where b.Passed=True
and DateDiff( 'd ',UpdateTime,#2007-3-21#) <7
group by Editor
order by count(articleid) desc
[解决办法]
加上子查询：
SELECT top 7 count(articleid) as Counts, Editor ,
(select count(*) from Article where Editor=b.Editor and Passed=True)
FROM Admin as a
INNER JOIN Article as b ON a.UserName = b.Editor
where b.Passed=True
and DateDiff( 'd ',UpdateTime,#2007-3-21#) <7
group by Editor
order by count(articleid) desc