34,576
社区成员
发帖
与我相关
我的任务
分享
现在要结果如下
低限 设备ID 当前采集值 严重低报警界限 低报警界限 高报警界限 严重高报警界限
ID EID CurrValue LLowAlarm LowAlarm HighAlarm HHighAlarm
1 1 11 10 null null null
2 1 11 null 20 null null
3 2 301 10 null 300 null
4 1 10001 10 null null 1000
5 2 5 10 40 500 null
6 1 11 null 10 null null
7 1 536 10 30 null null
现在要结果如下
低限 设备ID 当前采集值 严重低报警界限 低报警界限 高报警界限 严重高报警界限 状态
ID EID CurrValue LLowAlarm LowAlarm HighAlarm HHighAlarm status
1 1 11 10 null null null 正常
2 1 11 null 20 null null 低报警
3 2 301 10 null 300 null 高报警
4 1 10001 10 null null 1000 严重高报警
5 2 5 10 40 500 null 严重低报警
6 1 11 null 10 null null 正常
7 1 536 10 30 null null 正常
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[EID] int,[CurrValue] int,[LLowAlarm] int,[LowAlarm] int,[HighAlarm] int,[HHighAlarm] int)
Insert #T
select 1,1,11,10,null,null,null union all
select 2,1,11,null,20,null,null union all
select 3,2,301,10,null,300,null union all
select 4,1,10001,10,null,null,1000 union all
select 5,2,5,10,40,500,null union all
select 6,1,11,null,10,null,null union all
select 7,1,536,10,30,null,null
Go
--测试数据结束
SELECT * ,
CASE WHEN [CurrValue] >= [HHighAlarm]
AND [HHighAlarm] IS NOT NULL THEN '严重高报警'
WHEN [CurrValue] >= [HighAlarm]
AND [HighAlarm] IS NOT NULL THEN '高报警'
WHEN [CurrValue] <= [LLowAlarm]
AND [LLowAlarm] IS NOT NULL THEN '严重低报警'
WHEN [CurrValue] <= [LowAlarm]
AND [LowAlarm] IS NOT NULL THEN '低报警'
ELSE '正常'
END AS 状态
FROM #T