select c.id,c.classname,COUNT(a.id) as zs from [classname] c inner join [article] a on a.classid=c.id group by c.id, c.classname order by count(a.id) desc
或者
select * from [class] where [id] in(SELECT top 10 classid FROM [article] group by classid order by count(id) desc) --须指定top参数