SQL VIEW 最新や最初データを表示したい

Question

都度お世話になっております。

急遽仕様が変更となったため新たにご質問させて頂きます。

Table名：案件B一覧Table
|ID(キー)|親ID|件名|No|状態|処理開始日|状態更新日|
|:--|:--:|--:|
|A1|A1|A案件0|0|完了|2018/11/30|2018/12/01|
|A2|A1|A案件1|1|完了|2018/12/1|2018/12/02|
|B1|B1|B案件0|0|完了|2018/12/1|2018/12/05|
|A3|A1|A案件2|2|進行|2018/12/9|2018/12/10|
|B2|B1|B案件1|1|進行|2018/12/11|2018/12/11|
|C1|C1|C案件1|1|完了|2018/12/20|2018/12/21|
|C2|C1|C案件2|2|削除|2018/12/31|2019/01/10|
|C3|C1|C案件2ver2|2|取消|2019/01/10|2019/01/11|
|C4|C1|C案件2ver3|2|進行|2019/01/12|2019/01/12|
|D1|D1|D案件0|0|完了|2018/12/10|2018/12/15|
|D2|D1|D案件1|1|削除|2019/02/11|2019/02/15|
|E1|E1|E案件2|2|進行|2019/02/15|2019/02/22|

このテーブルを親IDを元に下記のようなビューを作りたいと考えています。
同じ親IDをキーとし状態を一覧表示する前回の質問で対応済み
件名は親IDでグループしたときの最新の値を取得したい。
逆に処理開始日は親IDでグループしたときの最初の値を取得したい。
View名：案件B状態確認View
|親ID|件名|0|1|2|処理開始日|
|:--|:--:|--:||
|A1|A案件2|完了|完了|進行|2018/11/30
|B1|B案件1|完了|進行||2018/12/1
|C1|C案件2ver3||完了|進行|2018/12/20
|D1|D案件1|完了|削除||2018/12/10
|E1|E案件2|||進行|2019/02/15

お力添えいただけますと幸いです。

Comments

[yoorwm]

案件の追加とか仕様変更とか、お仕事の依頼みたいですね。

[yambejp]

親IDの命名方法が良くないと思います
自IDと同じ親IDが同じなのは論理的におかしいので間違いのもと
（まぁあくまでダミーなのでしょうけど）

[Orlofsky]

SQLの動作確認が必要な質問ですから、 https://teratail.com/questions/175927 に回答してくれた sazi さんのように CREATE TABLEとINSERT を質問に載せては？

Answer 1

今度はいかがでしょう。

SELECT Q.親ID, Q.件名
, MAX(CASE Q.No WHEN 0 THEN Q.状態 END) "0"
, MAX(CASE Q.No WHEN 1 THEN Q.状態 END) "1"
, MAX(CASE Q.No WHEN 2 THEN Q.状態 END) "2"
, MIN(Q.処理開始日) 処理開始日
FROM (
SELECT T.親ID, T.No
, FIRST_VALUE(T.件名) OVER(PARTITION BY T.親ID ORDER BY T.状態更新日 DESC) 件名
, FIRST_VALUE(T.状態) OVER(PARTITION BY T.親ID, T.No ORDER BY T.状態更新日 DESC) 状態
, MIN(T.処理開始日) 処理開始日
FROM 案件B一覧Table T
GROUP BY T.親ID, T.No
) Q
GROUP BY Q.親ID, Q.件名

Comments

[sazi]

状態って、進行・削除・取消・完了という文字列なので、単にMaxというのは。

[sazi]

そもそも、最大というのは、「同じ親ID、Noの場合は状態更新日を元に最も新しいデータの状態」ですし。

[yorozuya]

MAX(状態) ←これなら「単にMAX」というご指摘が当たりますが、
MAX(CASE No WHEN 0 THEN 状態 END) ←こちらの意味をお解りですか？

[sazi]

caseの結果で取得される状態をMAXしてますよね
試された上での回答をお薦めします。

[yorozuya]

すみません。今試す環境がありませんので、試した結果を教えていただけますか？

[sazi]

それは、ご自身で試す事です。

[yorozuya]

＞試す環境がありませんので、
どうなる想定なのか、教えて頂けると幸いです。

[sazi]

コメントした通りなのですけど。
Max()を使うなら対象となるのは、状態更新日である事。
そもそも、(進行,削除,取消,完了)という全ての文字列に対して最大を求めるのはおかしい事。
文字コードがutf8なら最大は'進行'になりますよ？

[yorozuya]

MAX(状態) ならご指摘の通りですが、
MAX(CASE No WHEN 0 THEN 状態 END) なら、
同じ親ID の No=0 の 状態 ですから ’進行’ にはならないと思います。

[sazi]

質問のデータはあくまでサンプルだという認識は無いのですね。
では、(親ID, No)=(C1,2)のように複数の状態がある時はどうですか？

[yorozuya]

＞(親ID, No)=(C1,2)のように複数の状態がある時
大変失礼しました。見落としていました。

[sazi]

そのSQLに対してコメントするのは、私の回答の趣旨に反するのでご自身でお願いします。

Answer 2

> 件名は親IDでグループしたときの最新の値を取得したい。
これは、前回の質問の内容でカラムの追加するだけですね。
間違っていたので削除

件名は親IDでグループしたときの最新の値を取得したい。

この一連の質問で、散々相関サブクエリーの例を回答しているんですから、理解できていれば出来るはずですよ。

逆に処理開始日は親IDでグループしたときの最初の値を取得したい。

これは、前回の質問の内容でカラムの追加するだけですね。

やってみたけど出来ないというなら、どのように出来ないかのかを質問に追記して下さい。

単にカラムの追加すら質問に含めているんですから、理解もしていないし試してもいないとしか思えません。
これも断言はできないので削除します。