どうも、あおです。
今回は、結合して取得したデータの内、キー項目ごとの
枝番の最大値のデータを取得することをメインとしたSQLです。
まずWITHを使ったSELECTで枝番の最大値を取得しておいて、
それだけだと項目が足りないので、内部結合して項目を追加で取得、
それをメインのSELECTと外部結合して、WHERE条件で
必要なレコードを取り出すといった流れです。
例として、実際のテーブルと抽出結果で説明します。
左が見積テーブル（MTR）、右が仕入テーブル（SIR）
見積テーブル（MTR）はMTNOがキー項目、
仕入テーブル（SIR）は、SIIRENO、SIIRENOEDAの2項目がキー項目。
結合項目は見積NO（MTNO）
見積テーブルに仕入テーブルを外部結合して、
仕入テーブルの各データも取得する。
その際、同じ仕入番号（SIIRENO）で枝番（SIIRENOEDA）が
異なるデータがある場合には、最大の枝番を取得します。
最大でない枝番のレコードは取得しないようにします。
取得結果イメージ
ここでポイントなのが、
見積番号（MTNO）に紐づく仕入番号（SIIRENO）は、
NULLのケースもあるということ。
これも取得したいので、WHEREでCASE指定して取得します。
仕入番号（SIIRENO）がNULLのデータを取得する用に、
LEFT JOINで仕入テーブル（SIR）も紐づけます。
取得SQLがこちら
コピペ用
WITH work1 AS(
SELECT
SIIRENO
,MAX(SIIRENOEDA) AS SIIRENOEDA
FROM SIR
GROUP BY SIIRENO
)
,work2 AS(
SELECT
SIIRENO
,SIIRENOEDA
,MTNO
,KINGAKU
,JYOKYO
,BIKOU
FROM SIR
)
,work3 AS(
SELECT
W2.SIIRENO
,W2.SIIRENOEDA
,W2.MTNO
,W2.KINGAKU
,W2.JYOKYO
,BIKOU
FROM work1 AS W1
INNER JOIN work2 AS W2
ON W1.SIIRENO = W2.SIIRENO
AND W1.SIIRENOEDA = W2.SIIRENOEDA
)
SELECT
MTR.MTNO
,W3.SIIRENO
,W3.SIIRENOEDA
,MTR.KINGAKU AS MtrKINGAKU
,MTR.JYOKYO AS MtrJYOKYO
,MTR.BIKOU AS MtrBIKOU
,W3.KINGAKU AS SirKINGAKU
,W3.JYOKYO AS SirJYOKYO
,W3.BIKOU AS SirBIKOU
FROM MTR
LEFT JOIN work3 AS W3
ON MTR.MTNO = W3.MTNO
LEFT JOIN SIR
ON MTR.MTNO = SIR.MTNO
WHERE MTR.MTNO = CASE WHEN SIR.SIIRENO IS NOT NULL THEN
CASE WHEN W3.SIIRENO IS NOT NULL THEN W3.MTNO ELSE '' END
ELSE '' END
OR SIR.SIIRENO IS NULL
