怎么取出机器开关暂停各状态变化后最后一条记录

如何取出机器开关暂停各状态变化后最后一条记录
设备产品状态日期时间本次状态时间本次产量
EqA X 关机 2013-2-23 15:31:00 0:05:00 0 --这条是我手工临时加的，只是为了数据中增加关机的状态
EqAA生产2013-2-2315:36:540:00:00 1
EqAA生产2013-2-2315:36:590:00:05 2
EqAA暂停2013-2-2315:37:040:00:02 0
EqAA暂停2013-2-2315:37:090:00:07 0
EqAA暂停2013-2-2315:37:140:00:12 0
EqAA暂停2013-2-2315:37:190:00:17 0
EqAA暂停2013-2-2315:37:240:00:22 0
EqAA暂停2013-2-2315:37:290:00:27 0
EqAA暂停2013-2-2315:37:340:00:32 0
EqAA暂停2013-2-2315:37:390:00:37 0
EqAA暂停2013-2-2315:37:440:00:43 0
EqAA暂停2013-2-2315:37:490:00:48 0
EqAA暂停2013-2-2315:37:540:00:53 0
EqAA暂停2013-2-2315:38:000:00:58 0
EqAA暂停2013-2-2315:38:050:01:03 0
EqAA暂停2013-2-2315:38:100:01:08 0
EqAB生产2013-2-2315:38:150:00:03 1
EqAB生产2013-2-2315:38:200:00:08 2
EqAB生产2013-2-2315:38:250:00:13 3
EqAB生产2013-2-2315:38:300:00:18 4
EqAB生产2013-2-2315:38:350:00:23 5
EqAB生产2013-2-2315:38:400:00:28 6
EqAB生产2013-2-2315:38:450:00:33 7
EqAB生产2013-2-2315:38:500:00:38 8
EqAB生产2013-2-2315:38:550:00:43 9
EqAB生产2013-2-2315:39:000:00:48 10
EqAB生产2013-2-2315:39:050:00:53 11
EqAB生产2013-2-2315:39:100:00:58 12
EqAB生产2013-2-2315:39:150:01:03 13
EqAB暂停2013-2-2315:39:200:00:00 0
EqAB暂停2013-2-2315:39:250:01:05 0

有表如上，表中数据是用设备上实时采集出的，设备可以生产各类产品，暂停状态用于维修和换产品，每次状态变化后本次时间和本次产量清零并重新开始计数。

现需查出表中每次状态变化后最后的这条数据用于时间和产量的统计。结果当如下表：
设备产品状态日期时间本次状态时间本次产量
EqA X 关机 2013-2-23 15:31:00 0:05:00 0

EqAA生产2013-2-2315:36:590:00:05 2
EqAA暂停2013-2-2315:38:100:01:08 0
EqAB生产2013-2-2315:39:150:01:03 13
EqAB暂停2013-2-2315:39:250:01:05 0

代码当如何写数据库为sql2005
[解决办法]

create table tb(设备 nvarchar(10),产品 nvarchar(10),状态 nvarchar(10),日期 nvarchar(10),时间 nvarchar(10),本次状态时间 nvarchar(10),本次产量 int)
insert into tb select 'EqA','X','关机','2013-2-23','15:31:00','0:05:00',0 --这条是我手工临时加的，只是为了数据中增加关机的状态
insert into tb select 'EqA','A','生产','2013-2-23','15:36:54','0:00:00',1
insert into tb select 'EqA','A','生产','2013-2-23','15:36:59','0:00:05',2
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:04','0:00:02',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:09','0:00:07',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:14','0:00:12',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:19','0:00:17',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:24','0:00:22',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:29','0:00:27',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:34','0:00:32',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:39','0:00:37',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:44','0:00:43',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:49','0:00:48',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:54','0:00:53',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:00','0:00:58',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:05','0:01:03',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:10','0:01:08',0
insert into tb select 'EqA','B','生产','2013-2-23','15:38:15','0:00:03',1
insert into tb select 'EqA','B','生产','2013-2-23','15:38:20','0:00:08',2
insert into tb select 'EqA','B','生产','2013-2-23','15:38:25','0:00:13',3
insert into tb select 'EqA','B','生产','2013-2-23','15:38:30','0:00:18',4
insert into tb select 'EqA','B','生产','2013-2-23','15:38:35','0:00:23',5
insert into tb select 'EqA','B','生产','2013-2-23','15:38:40','0:00:28',6
insert into tb select 'EqA','B','生产','2013-2-23','15:38:45','0:00:33',7
insert into tb select 'EqA','B','生产','2013-2-23','15:38:50','0:00:38',8
insert into tb select 'EqA','B','生产','2013-2-23','15:38:55','0:00:43',9
insert into tb select 'EqA','B','生产','2013-2-23','15:39:00','0:00:48',10
insert into tb select 'EqA','B','生产','2013-2-23','15:39:05','0:00:53',11
insert into tb select 'EqA','B','生产','2013-2-23','15:39:10','0:00:58',12
insert into tb select 'EqA','B','生产','2013-2-23','15:39:15','0:01:03',13
insert into tb select 'EqA','B','暂停','2013-2-23','15:39:20','0:00:00',0
insert into tb select 'EqA','B','暂停','2013-2-23','15:39:25','0:01:05',0
go
select * from tb a where exists(select 1 from tb where 设备=a.设备 and 产品=a.产品 and 日期+' '+时间>a.日期+' '+a.时间 and 状态<>a.状态)

and not exists(select 1 from tb where 设备=a.设备 and 产品=a.产品 and 日期+' '+时间>a.日期+' '+a.时间 and 状态=a.状态)
or not exists(select 1 from tb where 设备=a.设备 and 产品=a.产品 and 日期+' '+时间>a.日期+' '+a.时间)
/*
设备产品状态日期时间本次状态时间本次产量
---------- ---------- ---------- ---------- ---------- ---------- -----------
EqA X 关机 2013-2-23 15:31:00 0:05:00 0
EqA A 生产 2013-2-23 15:36:59 0:00:05 2
EqA A 暂停 2013-2-23 15:38:10 0:01:08 0
EqA B 生产 2013-2-23 15:39:15 0:01:03 13
EqA B 暂停 2013-2-23 15:39:25 0:01:05 0

(5 行受影响)

*/
go
drop table tb

[解决办法]


with t as (
select *,rankid= rank() over(partition by 设备,产品,状态 order by 日期+' '+时间) from tb
)
select * from t where rankid=1
order by 日期+' '+时间

[解决办法]
这个对不对

insert into tb select 'EqA','A','暂停','2013-2-23','15:37:49','0:00:48',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:37:54','0:00:53',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:00','0:00:58',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:05','0:01:03',0
insert into tb select 'EqA','A','暂停','2013-2-23','15:38:10','0:01:08',0
insert into tb select 'EqA','B','生产','2013-2-23','15:38:15','0:00:03',1
insert into tb select 'EqA','B','生产','2013-2-23','15:38:20','0:00:08',2
insert into tb select 'EqA','B','生产','2013-2-23','15:38:25','0:00:13',3
insert into tb select 'EqA','B','生产','2013-2-23','15:38:30','0:00:18',4
insert into tb select 'EqA','B','生产','2013-2-23','15:38:35','0:00:23',5
insert into tb select 'EqA','B','生产','2013-2-23','15:38:40','0:00:28',6
insert into tb select 'EqA','B','生产','2013-2-23','15:38:45','0:00:33',7
insert into tb select 'EqA','B','生产','2013-2-23','15:38:50','0:00:38',8
insert into tb select 'EqA','B','生产','2013-2-23','15:38:55','0:00:43',9
insert into tb select 'EqA','B','生产','2013-2-23','15:39:00','0:00:48',10
insert into tb select 'EqA','B','生产','2013-2-23','15:39:05','0:00:53',11
insert into tb select 'EqA','B','生产','2013-2-23','15:39:10','0:00:58',12
insert into tb select 'EqA','B','生产','2013-2-23','15:39:15','0:01:03',13
insert into tb select 'EqA','B','暂停','2013-2-23','15:39:20','0:00:00',0
insert into tb select 'EqA','B','暂停','2013-2-23','15:39:25','0:01:05',0

select t1.*
from tb t1
inner join (
select 设备,产品,状态,max( cast(日期 + ' ' + 时间 as datetime ) ) as time1
from tb
group by 设备,产品,状态
) t2 on t2.设备 = t1.设备 and t2.产品 = t1.产品 and t2.状态 = t1.状态 and DATEDIFF(SECOND,t1.日期 + ' ' + t1.时间 ,time1)=0

drop table tb

设备产品状态日期时间本次状态时间本次产量
EqAA生产2013-2-2315:36:590:00:052
EqAA暂停2013-2-2315:38:100:01:080
EqAB生产2013-2-2315:39:150:01:0313
EqAB暂停2013-2-2315:39:250:01:050
EqAX关机2013-2-2315:31:000:05:000

怎么取出机器开关暂停各状态变化后最后

热点推荐