GROUP BYのキーに指定していない項目の出力を条件によって分岐させたい。

Question

前提・実現したいこと

以下の2つの表を「ID」、「SHIMEI」で結合し、「BASE」項目は後述する条件で出力する値を分岐させるビューを作成しています。

「WORKERテーブル」

ID[char(2)]	SHIMEI[char(10)]
01	taro

「SALARY」テーブル
|ID[char(2)]|SHIMEI[char(10)]|CODE[NUMBER(1)]|BASE[NUMBER(5)]|
|:--|:--:|--:|
|01|taro|1|300|
|01|taro|2|200|
|01|taro|3|100|

「BASE」項目の出力条件
0. CODE=2の「BASE」がnullの場合かつ、CODE=3の「BASE」がnullの場合、CODE=1の「BASE」を出力

1. CODE=2の「BASE」がnullでない場合かつ、CODE=3の「BASE」がnullの場合、CODE=2の「BASE」を出力
2. CODE=2の「BASE」がnullの場合かつ、CODE=3の「BASE」がnullでない場合、CODE=3の「BASE」を出力
3. CODE=2の「BASE」がnullでない場合かつ、CODE=3の「BASE」がnullでない場合、どちらか大きい方の「BASE」を出力

発生している問題・エラーメッセージ

上記の表の場合、**条件4.**が当てはまる想定でビューを作成していましたが、結果として「BASE」項目の中で一番大きな値が出力されてしまいます。

期待する結果

ID	SHIMEI	RESULT_BASE
01	taro	200

該当のソースコード

CREATE
OR REPLACE VIEW TEKIYOVIEW AS
SELECT
  WO.ID,
  WO.SHIMEI,
  SA.RESULT_BASE
FROM
  WORKER WO
  INNER JOIN (
    SELECT
      ID,
      SHIMEI,
      max(
        (
          CASE
            WHEN EDIT.CHUKAN_BASE_01 IS NOT NULL AND EDIT.CHUKAN_BASE_02 IS NULL AND EDIT.CHUKAN_BASE_03 IS NULL THEN EDIT.CHUKAN_BASE_01
            WHEN EDIT.CHUKAN_BASE_01 IS NULL AND EDIT.CHUKAN_BASE_02 IS NOT NULL AND EDIT.CHUKAN_BASE_03 IS NULL THEN EDIT.CHUKAN_BASE_02
            WHEN EDIT.CHUKAN_BASE_01 IS NULL AND EDIT.CHUKAN_BASE_02 IS NULL AND EDIT.CHUKAN_BASE_03 IS NOT NULL THEN EDIT.CHUKAN_BASE_03
            ELSE NULL
          END
        )
      ) RESULT_BASE
    FROM
      (
        SELECT
          ID,
          SHIMEI,
          (
            CASE
              WHEN YOKOMOCHI.BASE_02 IS NULL
              AND YOKOMOCHI.BASE_03 IS NULL THEN YOKOMOCHI.BASE_01
              ELSE NULL
            END
          ) CHUKAN_BASE_01,
          (
            CASE
              WHEN YOKOMOCHI.BASE_02 IS NOT NULL
              AND YOKOMOCHI.BASE_03 IS NOT NULL
              AND YOKOMOCHI.BASE_02 > YOKOMOCHI.BASE_03 THEN YOKOMOCHI.BASE_02
              ELSE NULL
            END
          ) CHUKAN_BASE_02,
          (
            CASE
              WHEN YOKOMOCHI.BASE_02 IS NOT NULL
              AND YOKOMOCHI.BASE_03 IS NOT NULL
              AND YOKOMOCHI.BASE_02 <= YOKOMOCHI.BASE_03 THEN YOKOMOCHI.BASE_02
              ELSE NULL
            END
          ) CHUKAN_BASE_03
        FROM
          (
            SELECT
              ID,
              SHIMEI,
              (
                CASE
                  CODE
                  WHEN 1 THEN BASE
                END
              ) BASE_01,
              (
                CASE
                  CODE
                  WHEN 2 THEN BASE
                END
              ) BASE_02,
              (
                CASE
                  CODE
                  WHEN 3 THEN BASE
                END
              ) BASE_03
            FROM
              SALARY
          ) YOKOMOCHI
      ) EDIT
    GROUP BY
      ID,
      SHIMEI
  ) SA ON WO.ID = SA.ID
  AND WO.SHIMEI = SA.SHIMEI;

試したこと

条件の文見直し、サブクエリ、max関数の使い方、GROUP BYの使い方等調べなおしましたが、
解決の糸口が見つからなかったため、こちらで質問させていただきます。

補足情報（FW/ツールのバージョンなど）

Oracle 12.1.0
Oracle SQL Developer 3.2.20.10

Answer 1

※手打ちかつ未検証です。悪しからず。

SELECT  WO.ID, WO.SHIMEI, SA.RESULT_BASE
FROM  WORKER WO
      INNER JOIN (
        SELECT ID, SHIMEI
             , coalesce(
                   case when BASE_02 > BASE_03 then BASE_02 else BASE_03 end
                 , BASE_02, BASE_03, BASE_01
               ) RESULT_BASE
        FROM (
              SELECT  ID, SHIMEI
                    , Max(CASE CODE WHEN 1 THEN BASE END) BASE_01
                    , Max(CASE CODE WHEN 2 THEN BASE END) BASE_02
                    , Max(CASE CODE WHEN 3 THEN BASE END) BASE_03
              FROM SALARY
              group by ID, SHIMEI
          ) YOKOMOCHI
        ) EDIT
  ) SA ON WO.ID = SA.ID AND WO.SHIMEI = SA.SHIMEI

Comments

[Orlofsky]

凄い、わたしは無駄に冗長なSQLを見る気が失せました。

[taketake08]

ご回答いただきありがとうございます！
教えていただいたコードから私が記載した不要な別名等を削除したところ希望する結果を取得することができました。
本当にありがとうございます。
group by やcoalesce関数についてもう一度学習し直します。
本当にありがとうございました。

[sazi]

縦を横にする所から躓いていたSQLでしたね。
後はSQLにおいての条件の本質を見極めると良いかと思います。

SQLも含めどんなコードもそうですけど、視覚的に分かり易くすることも大事です。

[taketake08]

アドバイスありがとうございます。
まだまだ駆け出しなので参考にさせていただきます！