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

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