求一个表组合查询的SQL语句

[yzflying]

表User 字段username,password,lastlogin...
表Article 字段title,content,username

这2个表通过username关联

现在对User表按照其username在Article发表文章总数进行降序排列，发表文章总数最多的排在第一。

[Houluoxuan_168]

select user,count(*) from Article group by UserName order by 2 desc

[dawugui]

[Quote=引用楼主 yzflying 的帖子:]
表User 字段username,password,lastlogin...
表Article 字段title,content,username

这2个表通过username关联

现在对User表按照其username在Article发表文章总数进行降序排列，发表文章总数最多的排在第一。
[/Quote]

select m.username , count(*) 发表文章总数
from User m,Article n
where m.username = n.username
group by username
order by 发表文章总数 desc , username

[Limpire]

select a.username from [User] a left join Article b on a.username=b.username group by a.username
order by sum(case when b.username is null then 0 else 1 end) desc

[zanyzyg]

select * from User,(select username,count(*) as allnum)t
where user.username=t.username
order by t.allnum desc

[wangxuelid]

[Quote=引用楼主 yzflying 的帖子:]
表User 字段username,password,lastlogin...
表Article 字段title,content,username

这2个表通过username关联

现在对User表按照其username在Article发表文章总数进行降序排列，发表文章总数最多的排在第一。
[/Quote]

select b.*  from  User  b inner join (select username ,count(*)as id_ from Article group by username  )Article 
on b.username =Article.username  order by Article.id 

[wangxuelid]

[Quote=引用楼主 yzflying 的帖子:]
表User 字段username,password,lastlogin...
表Article 字段title,content,username

这2个表通过username关联

现在对User表按照其username在Article发表文章总数进行降序排列，发表文章总数最多的排在第一。
[/Quote]

select b.*  from  User  b inner join (select username ,count(*)as id_ from Article group by username  )Article 
on b.username =Article.username  order by Article.id 

[bugprophet]

select m.username , count(*) 发表文章总数
from User m left join Article n
on m.username = n.username
group by username
order by 发表文章总数 desc , username

[wukele]

the question is simple