回答編集履歴

1 修正

yambejp

yambejp score 57380

2017/03/03 23:17  投稿

曲解していたらスミマセン。以下のような感じでしょうか?
- 元データ
```SQL
create table tbl(id int primary key,name varchar(20),dt datetime,status varchar(10),index(name,dt));
insert into tbl values
(1,'c','2017-03-03 21:46:55','live'),
(2,'a','2017-03-03 21:46:57','live'),
(3,'b','2017-03-03 21:46:59','live'),
(4,'c','2017-03-03 21:47:01','live'),
(5,'a','2017-03-03 21:47:03','live'),
(6,'d','2017-03-03 21:47:06','live'),
(7,'c','2017-03-03 21:47:08','live'),
(8,'d','2017-03-03 21:47:10','live'),
(9,'d','2017-03-03 21:47:12','live'),
(10,'b','2017-03-03 21:47:14','live'),
(11,'b','2017-03-03 21:47:16','live'),
(12,'c','2017-03-03 21:47:18','live'),
(13,'b','2017-03-03 21:47:20','live'),
(14,'d','2017-03-03 21:47:22','live'),
(15,'b','2017-03-03 21:47:24','dead'),
(16,'a','2017-03-03 21:47:26','dead'),
(17,'c','2017-03-03 21:47:28','dead'),
(18,'d','2017-03-03 21:47:30','dead'),
(19,'d','2017-03-03 21:47:32','dead'),
(20,'b','2017-03-03 21:47:34','dead'),
(21,'c','2017-03-03 21:48:17','dead'),
(22,'a','2017-03-03 21:48:19','dead'),
(23,'a','2017-03-03 21:48:21','dead'),
(24,'c','2017-03-03 21:48:23','dead'),
(25,'a','2017-03-03 21:48:25','dead'),
(26,'d','2017-03-03 21:48:27','dead'),
(27,'c','2017-03-03 21:48:29','dead'),
(28,'d','2017-03-03 21:48:31','dead'),
(29,'d','2017-03-03 21:48:33','dead'),
(30,'d','2017-03-03 21:48:35','dead'),
(31,'b','2017-03-03 21:48:37','live'),
(32,'b','2017-03-03 21:48:39','live'),
(33,'c','2017-03-03 21:48:41','live'),
(34,'d','2017-03-03 21:48:43','live'),
(35,'b','2017-03-03 21:48:46','live'),
(36,'a','2017-03-03 21:48:48','live'),
(37,'b','2017-03-03 21:48:50','live'),
(38,'b','2017-03-03 21:48:52','live'),
(39,'a','2017-03-03 21:48:54','live'),
(40,'b','2017-03-03 21:48:56','live'),
(41,'b','2017-03-03 21:48:58','live'),
(42,'c','2017-03-03 21:49:00','live'),
(43,'a','2017-03-03 21:49:02','live'),
(44,'c','2017-03-03 21:49:04','live'),
(45,'b','2017-03-03 21:49:06','live'),
(46,'a','2017-03-03 21:49:08','live'),
(47,'d','2017-03-03 21:49:10','dead'),
(48,'c','2017-03-03 21:49:12','dead'),
(49,'b','2017-03-03 21:49:14','dead'),
(50,'b','2017-03-03 21:49:16','dead'),
(51,'b','2017-03-03 21:49:19','dead'),
(52,'d','2017-03-03 21:49:21','dead'),
(53,'d','2017-03-03 21:49:23','dead'),
(54,'d','2017-03-03 21:49:25','dead'),
(55,'c','2017-03-03 21:49:27','dead'),
(56,'b','2017-03-03 21:49:29','dead'),
(57,'a','2017-03-03 21:49:31','dead'),
(58,'a','2017-03-03 21:49:33','dead'),
(59,'d','2017-03-03 21:49:35','dead'),
(60,'c','2017-03-03 21:49:37','dead'),
(61,'c','2017-03-03 21:49:39','live'),
(62,'c','2017-03-03 21:49:41','live'),
(63,'a','2017-03-03 21:49:43','live'),
(64,'d','2017-03-03 21:49:46','live'),
(65,'c','2017-03-03 21:49:48','live'),
(66,'c','2017-03-03 21:49:50','live'),
(67,'c','2017-03-03 21:49:52','live'),
(68,'d','2017-03-03 21:49:54','live'),
(69,'c','2017-03-03 21:49:56','live'),
(70,'d','2017-03-03 21:49:58','live');
```
# 修正  
前後がいる?  
- viewを用意します  
```SQL  
create view vw as select id,name,dt,status,(select count(*)+1 from tbl as t2 where t2.name=t1.name and t2.dt<t1.dt) as rank from tbl as t1;  
```  
- 遷移抽出
nameごとに遷移があった箇所を抽出
nameごとに前後いずれかに遷移があったレコードを抽出
```SQL
select *
from (select id,name,status,(select count(*)+1 from tbl as t2 where t2.name=t1.name and t2.dt<t1.dt) as rank from tbl as t1) as t3
inner join (select id,name,status,(select count(*)+1 from tbl as t5 where t5.name=t4.name and t5.dt<t4.dt) as rank from tbl as t4) as t6
on t3.name=t6.name and t3.rank+1=t6.rank
and not(t3.status=t6.status)
```
select v2.id,v2.rank,v2.name,v2.dt
,v1.status as beforestatus
,v2.status
,v3.status as afterstatus
from vw as v1
inner join vw as v2 on v1.name=v2.name and v1.rank+1=v2.rank
inner join vw as v3 on v2.name=v3.name and v2.rank+1=v3.rank
where not(v1.status=v2.status) or not(v2.status=v3.status)
order by v2.name,v2.id
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る