SQL不排序取出一列中前几个最大值

[Queena_Qiu]

SQL语句如下：
declare @AlarmTypes nvarchar(255)

IF OBJECT_ID('tempdb..#AlarmType') is not null
drop table #AlarmType

select * into tempdb..#AlarmType from
(select id = 1,告警类型+':'+convert(varchar(max),数量) as AlarmTypeNum
from (select c.告警类型,count(1) 数量
from 事件信息表 a with(nolock),遥信描述表 b with(nolock),告警类型表 c with(nolock),设备信息表 d with(nolock),终端信息表 e with(nolock),配电房信息表 f with(nolock)
where b.设备类型 = a.设备类型
and b.告警ID = a.遥信ID
and b.遥信值 = a.当前遥信值
and b.设备类型 = c.设备类型
and b.告警id = c.告警id
and a.DeviceID = d.DeviceID
and d.TerminalID = e.TerminalID
and e.RoomID = f.RoomID
and (a.事件发生时间 between '2017-06-01 00:00:00' and '2017-06-30 23:59:59')
and c.告警类型 != '开门（关门）事件'
group by b.设备类型,b.告警ID,c.告警类型) A) B

select @AlarmTypes = stuff((select ','+AlarmTypeNum from #AlarmType where c.id = id for xml path('')),1,1,'') from #AlarmType c group by c.id

我要取出数量最大的4个告警类型，输出结果类似为：低电压告警:15,A相欠压告警:4,B相欠压告警:4,C相欠压告警:4,环境温度过低告警:8,环境湿度过高告警:9；写入临时表时没办法排序，有什么解决办法吗？

[繁花尽流年]

你希望怎么排

[Queena_Qiu]

想要数量倒序排序，然后用top 4取出前四个，或者有其他方法也行

[moqing]

declare @AlarmTypes nvarchar(255) IF OBJECT_ID('tempdb..#AlarmType') is not null drop table #AlarmType select * into tempdb..#AlarmType from (select TOP 4 id = 1,告警类型+':'+convert(varchar(max),数量) as AlarmTypeNum from (select c.告警类型,count(1) 数量 from 事件信息表 a with(nolock),遥信描述表 b with(nolock),告警类型表 c with(nolock) ,设备信息表 d with(nolock),终端信息表 e with(nolock),配电房信息表 f with(nolock) where b.设备类型 = a.设备类型 and b.告警ID = a.遥信ID and b.遥信值 = a.当前遥信值 and b.设备类型 = c.设备类型 and b.告警id = c.告警id and a.DeviceID = d.DeviceID and d.TerminalID = e.TerminalID and e.RoomID = f.RoomID and (a.事件发生时间 between '2017-06-01 00:00:00' and '2017-06-30 23:59:59') and c.告警类型 != '开门（关门）事件' group by b.设备类型,b.告警ID,c.告警类型) A ORDER by AlarmTypeNum ) B select @AlarmTypes = stuff((select ','+AlarmTypeNum from #AlarmType where c.id = id for xml path('')),1,1,'') from #AlarmType c group by c.id 修改红色部份

[moqing]

ORDER by AlarmTypeNum DESC

[Queena_Qiu]

子句中用order by 会报错的，已经找到一个办法了，用ROW_NUMBER() over(order by 数量 desc)可以排序

[小野马1209]

逻辑就是排序后，再取Top数据。

[繁花尽流年]

引用 5 楼 Queena_Qiu 的回复:

子句中用order by 会报错的，已经找到一个办法了，用ROW_NUMBER() over(order by 数量 desc)可以排序

自问自答啊