回答編集履歴
1
修正
answer
CHANGED
|
@@ -76,14 +76,23 @@
|
|
|
76
76
|
(69,'c','2017-03-03 21:49:56','live'),
|
|
77
77
|
(70,'d','2017-03-03 21:49:58','live');
|
|
78
78
|
```
|
|
79
|
+
# 修正
|
|
80
|
+
前後がいる？
|
|
81
|
+
- viewを用意します
|
|
82
|
+
```SQL
|
|
83
|
+
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;
|
|
84
|
+
```
|
|
79
85
|
|
|
80
86
|
- 遷移抽出
|
|
81
|
-
nameごとに遷移があった
|
|
87
|
+
nameごとに前後いずれかに遷移があったレコードを抽出
|
|
82
88
|
```SQL
|
|
89
|
+
select v2.id,v2.rank,v2.name,v2.dt
|
|
90
|
+
,v1.status as beforestatus
|
|
83
|
-
|
|
91
|
+
,v2.status
|
|
92
|
+
,v3.status as afterstatus
|
|
93
|
+
from vw as v1
|
|
84
|
-
|
|
94
|
+
inner join vw as v2 on v1.name=v2.name and v1.rank+1=v2.rank
|
|
85
|
-
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
|
|
86
|
-
on
|
|
95
|
+
inner join vw as v3 on v2.name=v3.name and v2.rank+1=v3.rank
|
|
87
|
-
|
|
96
|
+
where not(v1.status=v2.status) or not(v2.status=v3.status)
|
|
88
|
-
|
|
97
|
+
order by v2.name,v2.id
|
|
89
|
-
```
|
|
98
|
+
```
|