如何取出机器开关暂停各状态变化后最后一条记录
设备产品状态日期 时间 本次状态时间本次产量
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 日期+' '+时间
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
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
