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；写入临时表时没办法排序，有什么解决办法吗？
7 个解决方案
#1
你希望怎么排
#2
想要数量倒序排序，然后用top 4取出前四个，或者有其他方法也行
#3
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
修改红色部份
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
修改红色部份
#4
ORDER by AlarmTypeNum DESC
#5
子句中用order by 会报错的，已经找到一个办法了，用ROW_NUMBER() over(order by 数量 desc)可以排序
#6
逻辑就是排序后，再取Top数据。
#7
自问自答啊
#1
你希望怎么排
#2
想要数量倒序排序，然后用top 4取出前四个，或者有其他方法也行
#3
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
修改红色部份
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
修改红色部份
#4
ORDER by AlarmTypeNum DESC
#5
子句中用order by 会报错的，已经找到一个办法了，用ROW_NUMBER() over(order by 数量 desc)可以排序
#6
逻辑就是排序后，再取Top数据。
#7
自问自答啊