SQL不排序取出一列中前几个最大值
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;写入临时表时没办法排序,有什么解决办法吗?