字段 网元 标题(默认为1)
a aaa
b bbb
c ccc
求,网元的最大的前5位 ,然后最大前5位中,标题最大的前5位
select top 5 [网元],count([标题]) as 数量 FROM [dbo].[table] group by [ObjectName] order by 数量 desc
最后想的倒 如:
网元 数量 标题
a 3000 ccc
b 2000 ddd
注:最后排出来的标题是某个最多网元中,比如a中最多的标题希望高手能帮下,在线等,麻烦大家!
a aaa
b bbb
c ccc
求,网元的最大的前5位 ,然后最大前5位中,标题最大的前5位
select top 5 [网元],count([标题]) as 数量 FROM [dbo].[table] group by [ObjectName] order by 数量 desc
最后想的倒 如:
网元 数量 标题
a 3000 ccc
b 2000 ddd
注:最后排出来的标题是某个最多网元中,比如a中最多的标题希望高手能帮下,在线等,麻烦大家!
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
(select top 5 [标题] from [dbo].[table] group by [标题] order by
count(*) desc) group by [网元] order by count(*) desc 刚刚这个sql出来了,但是,不能显示标题,就差显示标题
网元 数量
CQGM341B7 1284398
CQGM341B3 946873
CQGM331B10 582378
CQGM111B14 461280
CQGM111B11 160416
猜下
你表结构是甚来的?select top 5 [网元],count(*),[标题] FROM [dbo].[table] where [标题] in
(select top 5 [标题] from [dbo].[table] group by [标题] order by
count(*) desc) group by [网元],[标题] order by count(*) desc
最后的结果应该是
网元 数量 标题
a 5000 aaa(3000)
b 3000 ddd(2000)
看这样我解释得明白不?
[标题]=(select top 1 [标题] from [dbo].[table] where [网元]=t.[网元] group by [标题] order by count(1) desc)
FROM [dbo].[table] as t order by 数量 desc
[标题]=(select top 1 [标题] from [dbo].[table] where [网元]=t.[网元] group by [标题] order by count(1) desc)
FROM [dbo].[table] as t group by [网元] order by 数量 desc
朋友这个sql我开始也这样写的,但是不对,不能让网元和标题同时分组,如果同时分组,得到的网元就有相同的了,
表结构
网元 标题
就这两个字段