我的项目是在网格中显示传感器状态，需要sql查询以显示我在下面提到的几种情况下的传感器状态 [英] my project is to display the sensor status in grid,need sql query to display status of sensors with few conditions which i mentioned below

问题描述

我的项目是在网格中显示传感器状态，每个传感器状态有3种状态1)OFF 2)LOAD 3)IDLE.这种情况完全基于电压，因此如果传感器为LOAD生成10个报告并且第11个报告为OFF，那么我只需要在grid中显示LOAD的第一个报告和OFF的第一个报告.所以我需要此任务的sql查询. br/>
预先谢谢您.

my project is to display the sensor status in grid,each sensor state has 3 states 1)OFF 2) LOAD 3)IDLE. This conditions is purely based on voltages so if a sensor generated 10 reports for LOAD and the 11th report is OFF so i need to display only 1st report of LOAD and 1st report of OFF in grid.so i need sql query for this task.

thank you in advance

推荐答案

此查询将向您显示每个设备上每个传感器的第一个事件.它会输出数据，以便您可以随心所欲地进行操作.

This query will show you the first event for each sensor per device. It will output data so that you can do whatever you want with it.

IF OBJECT_ID(N'tempdb..#s1') IS NOT NULL
  drop table #s1

select ROW_NUMBER() over (partition by '1' order by imeino, sensor ,timestamp) rid, *  into #s1 from
(
select autoid, imeino , deviceid , timestamp, 's-1' sensor, [s-1] status   from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-2' sensor, [s-2] status   from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-3' sensor, [s-3] status   from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-4' sensor, [s-4] status   from sensordata
) as flatted
 where not imeino is null

;with a(rid, autoid, imeino, deviceid, timestamp, sensor, status, r) as
(
select * , RANK() over (partition by imeino, sensor, status order by rid) r from #s1
)
select * from a
where r = 1

我以为您的表名称是sensordata，因此请将其更改为任何名称.

如果您不能将其转换为存储过程，那么就不应该得到一个好成绩...

希望对您有所帮助.

I assumed that your table name is sensordata so change it to whatever it is.

If you can''t convert it to stored procedure so you do not deserve to get a good score...

Hope it helps.

这篇关于我的项目是在网格中显示传感器状态，需要sql查询以显示我在下面提到的几种情况下的传感器状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！