回答編集履歴
1
修正
test
CHANGED
|
@@ -154,24 +154,44 @@
|
|
|
154
154
|
|
|
155
155
|
```
|
|
156
156
|
|
|
157
|
+
# 修正
|
|
158
|
+
|
|
159
|
+
前後がいる？
|
|
160
|
+
|
|
161
|
+
- viewを用意します
|
|
162
|
+
|
|
163
|
+
```SQL
|
|
164
|
+
|
|
165
|
+
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;
|
|
166
|
+
|
|
167
|
+
```
|
|
168
|
+
|
|
157
169
|
|
|
158
170
|
|
|
159
171
|
- 遷移抽出
|
|
160
172
|
|
|
161
|
-
nameごとに遷移があった
|
|
173
|
+
nameごとに前後いずれかに遷移があったレコードを抽出
|
|
162
174
|
|
|
163
175
|
```SQL
|
|
164
176
|
|
|
165
|
-
select
|
|
177
|
+
select v2.id,v2.rank,v2.name,v2.dt
|
|
166
178
|
|
|
167
|
-
|
|
179
|
+
,v1.status as beforestatus
|
|
168
180
|
|
|
169
|
-
|
|
181
|
+
,v2.status
|
|
170
182
|
|
|
171
|
-
|
|
183
|
+
,v3.status as afterstatus
|
|
172
184
|
|
|
185
|
+
from vw as v1
|
|
186
|
+
|
|
187
|
+
inner join vw as v2 on v1.name=v2.name and v1.rank+1=v2.rank
|
|
188
|
+
|
|
189
|
+
inner join vw as v3 on v2.name=v3.name and v2.rank+1=v3.rank
|
|
190
|
+
|
|
173
|
-
|
|
191
|
+
where not(v1.status=v2.status) or not(v2.status=v3.status)
|
|
192
|
+
|
|
193
|
+
order by v2.name,v2.id
|
|
194
|
+
|
|
195
|
+
```
|
|
174
196
|
|
|
175
197
|
|
|
176
|
-
|
|
177
|
-
```
|